﻿Imports System.Data.SqlClient
Imports DMC.Database
Public Class clsInSo
    'Dim s As String = "Server=DMC-THUY\SAD8484;Database=DMCLand; Integrated Security=SSPI"
    'Dim s As String = "Server=DMC-THUY\SAD8484;Database=DMCLand;User ID=sa;Password=123456"
    'Dim s As String = "Server=dmcthanh;Database=DMCLand20081083;User ID=sa;Password=''"
    ' Dim s As String = "Server=DMC-SVR\MAP;Database=georgetown;User ID=sa;Password=1"
    ' Public connect As New SqlConnection(s)
    Public bien As Integer = 0
    Public da As New SqlDataAdapter
    Public ds As New DataSet
    ' Public Mydata As New DataTable
    Public cmd As New SqlCommand
    Public sToBanDo As String = ""
    Public iDem As Integer = 0
    Public TangI As Integer = 0
    Public conn As SqlConnection
    Private strConnection As String = ""
    Private strSoThua As String = ""
    Private strMaHoSoCapGCN As String = ""
    Private strMaChu As String = ""
    Private strToBanDo As String = ""
    Private strFlag As Int16
    Private strUsername As String = ""
    Private strDonViHangChinh As String = ""
   
    Public Property DonViHanhChinh() As String
        Get
            Return strDonViHangChinh
        End Get
        Set(ByVal value As String)
            strDonViHangChinh = value
        End Set
    End Property
    Public Property UserName() As String
        Get
            Return strUsername
        End Get
        Set(ByVal value As String)
            strUsername = value
        End Set
    End Property
    Public Property Connection() As String
        Get
            Return strConnection
        End Get
        Set(ByVal value As String)
            strConnection = value
        End Set
    End Property
    Public Property Flag() As Int16
        Get
            Return strFlag
        End Get
        Set(ByVal value As Int16)
            strFlag = value
        End Set
    End Property
    Public Property SoThua() As String
        Get
            Return strSoThua
        End Get
        Set(ByVal value As String)
            strSoThua = value
        End Set
    End Property
    Public Property MaHoSoCapGCN() As String
        Get
            Return strMaHoSoCapGCN
        End Get
        Set(ByVal value As String)
            strMaHoSoCapGCN = value
        End Set
    End Property
    Public Property ToBanDo() As String
        Get
            Return strToBanDo
        End Get
        Set(ByVal value As String)
            strToBanDo = value
        End Set
    End Property
    Public Function SelectDVHC() As DataTable
        Dim dt As New DataTable
        Dim strError As String = ""
        Dim ParaDVHC() As String = {"@MaDVHC"}
        Try
            Dim Values() As String = {strDonViHangChinh}
            dt = GetData_SP("spSelectHuyenTinh", Values, ParaDVHC)
            strError = ""
        Catch ex As Exception
            strError = ex.Message
        End Try
        Return dt
    End Function
#Region "SoCapGCN"
    Public Sub TrangMoDauSoCapGCN(ByVal app As Object, ByVal DonViHanhChinh() As String)
        Dim tinh As String = DonViHanhChinh(0)
        Dim huyen As String = DonViHanhChinh(1)
        Dim xa As String = DonViHanhChinh(2)
        Dim matinh01 As Integer = 0
        Dim matinh02 As Integer = 1

        Dim mahuyen01 As Integer = 0
        Dim mahuyen02 As Integer = 0
        Dim mahuyen03 As Integer = 1

        Dim maxa01 As Integer = 0
        Dim maxa02 As Integer = 0
        Dim maxa03 As Integer = 0
        Dim maxa04 As Integer = 2
        Dim maxa05 As Integer = 8
        Dim soquyen01 As String = 0
        Dim soquyen02 As String = 1
        Dim soquyen03 As String = 0

        Dim ngay As String = CStr(Now.Day)
        If Len(ngay) = 1 Then ngay = "0" + ngay

        Dim thang As String = CStr(Now.Month)
        If Len(thang) = 1 Then thang = "0" + thang


        With app
            .Cells.Font.Size = 12

            .Cells(1, "A").ColumnWidth = 5
            .Cells(1, "B").ColumnWidth = 11.99
            .Cells(1, "C").ColumnWidth = 12.8
            .Cells(1, "D").ColumnWidth = 12.8
            .Cells(1, "E").ColumnWidth = 3.57
            .Cells(1, "F").ColumnWidth = 5
            .Cells(1, "G").ColumnWidth = 11
            .Cells(1, "H").ColumnWidth = 11
            .Cells(1, "I").ColumnWidth = 4.29
            .Cells(1, "J").ColumnWidth = 4.29
            .Cells(1, "K").ColumnWidth = 4.29
            .Cells(1, "L").ColumnWidth = 4.29
            .Cells(1, "M").ColumnWidth = 4.29
            .Cells(1, "N").ColumnWidth = 4.29
            .Cells(1, "O").ColumnWidth = 6.29
            .Cells(1, "P").ColumnWidth = 6.29

            .Range(.Cells(1, "A"), .Cells(38, "P")).VerticalAlignment = -4160
            .Range(.Cells(1, "A"), .Cells(24, "P")).HorizontalAlignment = -4108

            .Range(.Cells(31, "B"), .Cells(36, "G")).HorizontalAlignment = -4131
            .Range(.Cells(31, "H"), .Cells(38, "H")).HorizontalAlignment = -4152

            .Range(.Cells(1, "E"), .Cells(3, "P")).Font.Bold = True
            .Range(.Cells(18, "A"), .Cells(24, "P")).Font.Bold = True
            .Range(.Cells(31, "b"), .Cells(38, "H")).Font.Bold = True
            'Ban hành kèm theo thông tư.......
            .Range(.Cells(1, "A"), .Cells(7, "D")).MergeCells = True
            .Range(.Cells(1, "A"), .Cells(7, "D")).Font.Size = 13
            .Range(.Cells(1, "A"), .Cells(7, "D")).Value = "Ban hành kèm theo thông tư                                                  Số 09/2007/TT-BTNMT                                                                              Ngày 02/08/2007 của Bộ Tài nguyên                                                           và Môi trường hướng dẫn lập, chỉnh lý,                                            quản lý hồ sơ địa chính"
            .Range(.Cells(1, "A"), .Cells(7, "D")).WrapText = True
            ' Cộng hòa xã hội chủ nghĩa việt nam...
            .Range(.Cells(1, "E"), .Cells(3, "M")).MergeCells = True
            .Range(.Cells(1, "E"), .Cells(3, "M")).Font.Size = 14
            .Range(.Cells(1, "E"), .Cells(3, "M")).Value = "CỘNG HÒA XÃ HỘI CHỦ NGHĨA VIỆT NAM                              Độc Lập - Tự Do - Hạnh Phúc"
            .Range(.Cells(1, "E"), .Cells(3, "M")).WrapText = True
            ' Mẫu số..
            .Range(.Cells(1, "N"), .Cells(2, "P")).MergeCells = True
            .Range(.Cells(1, "N"), .Cells(2, "P")).Font.Size = 14
            .Range(.Cells(1, "N"), .Cells(2, "P")).Value = "Mẫu số: 09/ĐK"
            'Tiêu đề
            .Range(.Cells(18, "B"), .Cells(28, "O")).MergeCells = True
            .Range(.Cells(18, "B"), .Cells(28, "O")).Font.Size = 30
            '.Range(.Cells(18, "B"), .Cells(28, "O")).Value = "SỔ                                                        CẤP GIẤY CHỨNG NHẬN                       QUYỀN SỬ DỤNG ĐẤT"
            .Range(.Cells(18, "B"), .Cells(28, "O")).Value = "SỔ                                                                 CẤP GIẤY CHỨNG NHẬN QUYỀN SỬ DỤNG ĐẤT QUYỀN SỞ HỮU NHÀ Ở VÀ TÀI SẢN KHÁC GẮN LIỀN VỚI ĐẤT"
            .Range(.Cells(18, "B"), .Cells(28, "O")).WrapText = True

            .Range(.Cells(31, "C"), .Cells(38, "N")).Font.Size = 14
            '.Range(.Cells(31, "J"), .Cells(38, "N")).Font.Size = 11
            .Range(.Cells(31, "J"), .Cells(38, "N")).HorizontalAlignment = -4108
            .Range(.Cells(31, "J"), .Cells(38, "N")).VerticalAlignment = -4108

            .Range(.Cells(31, "C"), .Cells(32, "C")).MergeCells = True
            .Range(.Cells(31, "C"), .Cells(32, "C")).Value = "TỈNH:"

            .Range(.Cells(31, "D"), .Cells(32, "G")).MergeCells = True
            .Range(.Cells(31, "D"), .Cells(32, "G")).Value = tinh

            .Range(.Cells(31, "H"), .Cells(32, "H")).MergeCells = True
            .Range(.Cells(31, "H"), .Cells(32, "H")).Value = "Mã:"
            .Range(.Cells(31, "J"), .Cells(31, "J")).Value = matinh01
            .Range(.Cells(31, "K"), .Cells(31, "K")).Value = matinh02

            .Range(.Cells(31, "J"), .Cells(31, "J")).BorderAround(, 2, -4105)
            .Range(.Cells(31, "K"), .Cells(31, "K")).BorderAround(, 2, -4105)

            .Range(.Cells(33, "C"), .Cells(34, "C")).MergeCells = True
            .Range(.Cells(33, "C"), .Cells(34, "C")).Value = "HUYỆN:"
            .Range(.Cells(33, "D"), .Cells(34, "G")).MergeCells = True
            .Range(.Cells(33, "D"), .Cells(34, "G")).Value = huyen

            .Range(.Cells(33, "H"), .Cells(34, "H")).MergeCells = True
            .Range(.Cells(33, "H"), .Cells(34, "H")).Value = "Mã:"
            .Range(.Cells(33, "J"), .Cells(33, "J")).Value = mahuyen01
            .Range(.Cells(33, "K"), .Cells(33, "K")).Value = mahuyen02
            .Range(.Cells(33, "L"), .Cells(33, "L")).Value = mahuyen03

            .Range(.Cells(33, "J"), .Cells(33, "J")).BorderAround(, 2, -4105)
            .Range(.Cells(33, "K"), .Cells(33, "K")).BorderAround(, 2, -4105)
            .Range(.Cells(33, "L"), .Cells(33, "L")).BorderAround(, 2, -4105)

            .Range(.Cells(35, "C"), .Cells(36, "C")).MergeCells = True
            .Range(.Cells(35, "C"), .Cells(36, "C")).Value = "XÃ:"
            .Range(.Cells(35, "D"), .Cells(36, "G")).MergeCells = True
            .Range(.Cells(35, "D"), .Cells(36, "G")).Value = xa

            .Range(.Cells(35, "H"), .Cells(36, "H")).MergeCells = True
            .Range(.Cells(35, "H"), .Cells(36, "H")).Value = "Mã:"
            .Range(.Cells(35, "J"), .Cells(35, "J")).Value = maxa01
            .Range(.Cells(35, "K"), .Cells(35, "K")).Value = maxa02
            .Range(.Cells(35, "L"), .Cells(35, "L")).Value = maxa03
            .Range(.Cells(35, "M"), .Cells(35, "M")).Value = maxa04
            .Range(.Cells(35, "N"), .Cells(35, "N")).Value = maxa05

            .Range(.Cells(35, "J"), .Cells(35, "J")).BorderAround(, 2, -4105)
            .Range(.Cells(35, "K"), .Cells(35, "K")).BorderAround(, 2, -4105)
            .Range(.Cells(35, "L"), .Cells(35, "L")).BorderAround(, 2, -4105)
            .Range(.Cells(35, "M"), .Cells(35, "M")).BorderAround(, 2, -4105)
            .Range(.Cells(35, "N"), .Cells(35, "N")).BorderAround(, 2, -4105)

            .Range(.Cells(37, "H"), .Cells(38, "H")).MergeCells = True
            .Range(.Cells(37, "H"), .Cells(38, "H")).Value = "Quyển số:"

            .Range(.Cells(37, "J"), .Cells(37, "J")).Value = soquyen01
            .Range(.Cells(37, "K"), .Cells(37, "K")).Value = soquyen02
            .Range(.Cells(37, "L"), .Cells(37, "L")).Value = soquyen03

            .Range(.Cells(37, "J"), .Cells(37, "J")).BorderAround(, 2, -4105)
            .Range(.Cells(37, "K"), .Cells(37, "K")).BorderAround(, 2, -4105)
            .Range(.Cells(37, "L"), .Cells(37, "L")).BorderAround(, 2, -4105)
            'Phan tieu de trang 1

            .Range(.Cells(68, "A"), .Cells(69, "P")).Font.Bold = True

            .Range(.Cells(68, "A"), .Cells(69, "A")).MergeCells = True
            .Range(.Cells(68, "A"), .Cells(69, "A")).Value = "Số thứ tự"
            .Range(.Cells(68, "A"), .Cells(69, "A")).WrapText = True
            .Range(.Cells(68, "A"), .Cells(69, "A")).BorderAround(, 2, -4105)
            .Range(.Cells(68, "A"), .Cells(104, "A")).HorizontalAlignment = -4108
            .Range(.Cells(68, "A"), .Cells(104, "A")).VerticalAlignment = -4108
            '----------------
            .Range(.Cells(68, "B"), .Cells(69, "D")).MergeCells = True
            .Range(.Cells(68, "B"), .Cells(69, "D")).Value = "Tên và địa chỉ                                                                                của người được cấp GCN"
            .Range(.Cells(68, "B"), .Cells(69, "D")).WrapText = True
            .Range(.Cells(68, "B"), .Cells(69, "D")).BorderAround(, 2, -4105)
            .Range(.Cells(68, "B"), .Cells(69, "D")).HorizontalAlignment = -4108
            .Range(.Cells(68, "B"), .Cells(69, "D")).VerticalAlignment = -4108
            '---------------------
            .Range(.Cells(68, "E"), .Cells(69, "F")).MergeCells = True
            .Range(.Cells(68, "E"), .Cells(69, "F")).Value = "Số phát               hành GCN"
            .Range(.Cells(68, "E"), .Cells(69, "F")).WrapText = True
            .Range(.Cells(68, "E"), .Cells(69, "F")).BorderAround(, 2, -4105)
            .Range(.Cells(68, "E"), .Cells(69, "F")).HorizontalAlignment = -4108
            '------------------------
            .Range(.Cells(68, "G"), .Cells(69, "G")).MergeCells = True
            .Range(.Cells(68, "G"), .Cells(69, "G")).Value = "Ngày ký                 GCN"
            .Range(.Cells(68, "G"), .Cells(69, "G")).WrapText = True
            .Range(.Cells(68, "G"), .Cells(69, "G")).BorderAround(, 2, -4105)
            .Range(.Cells(68, "G"), .Cells(69, "G")).HorizontalAlignment = -4108
            '--------------
            .Range(.Cells(68, "H"), .Cells(69, "H")).MergeCells = True
            .Range(.Cells(68, "H"), .Cells(69, "H")).Value = "Ngày giao                    GCN"
            .Range(.Cells(68, "H"), .Cells(69, "H")).WrapText = True
            .Range(.Cells(68, "H"), .Cells(69, "H")).BorderAround(, 2, -4105)
            .Range(.Cells(68, "H"), .Cells(69, "H")).VerticalAlignment = -4108
            .Range(.Cells(68, "H"), .Cells(69, "H")).HorizontalAlignment = -4108
            '---------------------
            .Range(.Cells(68, "I"), .Cells(69, "L")).MergeCells = True
            .Range(.Cells(68, "I"), .Cells(69, "L")).Value = "Họ tên, chữ ký của người nhận GCN "
            .Range(.Cells(68, "I"), .Cells(69, "L")).WrapText = True
            .Range(.Cells(68, "I"), .Cells(69, "L")).BorderAround(, 2, -4105)
            .Range(.Cells(68, "I"), .Cells(69, "L")).VerticalAlignment = -4108
            .Range(.Cells(68, "I"), .Cells(69, "L")).HorizontalAlignment = -4108
            '-------------------
            .Range(.Cells(68, "M"), .Cells(69, "P")).MergeCells = True
            .Range(.Cells(68, "M"), .Cells(69, "P")).Value = "Ghi chú"
            .Range(.Cells(68, "M"), .Cells(69, "P")).BorderAround(, 2, -4105)
            .Range(.Cells(68, "M"), .Cells(69, "P")).HorizontalAlignment = -4108
            .Range(.Cells(68, "M"), .Cells(69, "P")).VerticalAlignment = -4108
        End With
    End Sub
    Public Sub ExpExSoCapGCN(ByVal app As Object, ByVal NamIn As Int16)
        Dim tongdong As Long ' = 170
        Dim iDongHienThoi As Integer = 67 + 3 ' 3 la tieu de trang 2
        Dim dst As New DataSet
        Dim dss As New DataSet
        Dim d, d1 As Integer
        Dim dm, dem As Integer
        Dim iTrang As Integer = 1 ' dang in trang 1
        Dim iCuoi As Integer = 0
        Dim iCuoi2 As Integer = 0
        '@Flag=1 
        'cmd.CommandType = CommandType.Text
        'cmd.CommandText = st
        'cmd.Connection = connect
        'da.SelectCommand = cmd
        'ds.Clear()
        'ds.Tables.Clear()

        'da.Fill(ds, "SoCapGCN")


        Dim MyTable As New DataTable
        Dim paras() As String = {"@Flag", "@NamIn", "@MaDVHC"}
        Dim Values() As String = {1, NamIn, strDonViHangChinh}

        MyTable = GetData_SP("spSoCapGCN", Values, paras)

        Dim MyTableTong As New DataTable
        Dim parasTong() As String = {"@Flag"}
        Dim ValuesTong() As String = {2}

        MyTableTong = GetData_SP("spSoCapGCN", ValuesTong, parasTong)

        tongdong = 68 + 2 + 1 + MyTableTong.Rows.Count + MyTable.Rows.Count

        If MyTable.Rows.Count > 0 Then
            ' tieu de chung cua cac trang
            TieuDeChungCacTrangSoCapGCN(app, tongdong)
        End If

        For i As Integer = 0 To MyTable.Rows.Count - 1

            Dim MyTableCSD As New DataTable
            Dim parasCSD() As String = {"@Flag", "@MaHoSoCapGCN", "@NamIn"}
            Dim ValuesCSD() As String = {3, MyTable.Rows(i).Item("MaHoSoCapGCN").ToString, NamIn}

            MyTableCSD = GetData_SP("spSoCapGCN", ValuesCSD, parasCSD)

            If MyTableCSD.Rows.Count > 0 Then



                Dim csd As String = ""
                dem = MyTableCSD.Rows.Count

                If iDongHienThoi = 68 + 68 * iTrang Then

                    iDongHienThoi += 1
                    d1 += 1
                    iTrang += 1
                End If

                dm = dem

                If i = 0 Then
                    d = 71 '56
                    d1 = d + dm
                Else
                    d = d1
                    d1 = d1 + dm
                End If

                For j As Integer = 0 To MyTableCSD.Rows.Count - 1

                    iDongHienThoi += 1

                    If MyTableCSD.Rows(j).Item("DoiTuongSDD").ToString <> DBNull.Value.ToString Then

                        If (MyTableCSD.Rows(j).Item("DoiTuongSDD").ToString <> "GDC" Or MyTableCSD.Rows(j).Item("GioiTinh").ToString = "") Then
                            csd = MyTableCSD.Rows(j).Item("HoTen").ToString
                        ElseIf ((MyTableCSD.Rows(j).Item("GioiTinh") = True)) Then
                            csd = "Ông " & MyTableCSD.Rows(j).Item("HoTen").ToString
                        ElseIf ((MyTableCSD.Rows(j).Item("GioiTinh") = False)) Then
                            csd = "Bà " & MyTableCSD.Rows(j).Item("HoTen").ToString
                        End If
                    Else
                        csd = MyTableCSD.Rows(j).Item("HoTen").ToString 'Cột đối tượng sử dụng bị để trống
                    End If

                    If iDongHienThoi = 68 + 68 * iTrang Then
                        iTrang += 1
                        iDongHienThoi += 2
                        d1 += 2
                    End If

                    With app
                        .Range(.Cells(iDongHienThoi, "B"), .Cells(iDongHienThoi, "D")).MergeCells = True
                        .Range(.Cells(iDongHienThoi, "B"), .Cells(iDongHienThoi, "D")).Value = csd
                        .Range(.Cells(iDongHienThoi, "E"), .Cells(iDongHienThoi, "F")).MergeCells = True
                        .Range(.Cells(iDongHienThoi, "I"), .Cells(iDongHienThoi, "L")).MergeCells = True
                        .Range(.Cells(iDongHienThoi, "M"), .Cells(iDongHienThoi, "P")).MergeCells = True

                        If iDongHienThoi = 68 + 68 * iTrang - 1 Then ' mergecells them dong trong tiep theo khi trang trang
                            .Range(.Cells(iDongHienThoi + 1, "B"), .Cells(iDongHienThoi + 1, "D")).MergeCells = True
                            .Range(.Cells(iDongHienThoi + 1, "E"), .Cells(iDongHienThoi + 1, "F")).MergeCells = True
                            .Range(.Cells(iDongHienThoi + 1, "I"), .Cells(iDongHienThoi + 1, "L")).MergeCells = True
                            .Range(.Cells(iDongHienThoi + 1, "M"), .Cells(iDongHienThoi + 1, "P")).MergeCells = True
                        End If

                    End With

                Next 'j

                iDongHienThoi += 1 ' het 1 ho so

                With app
                    'In Số thứ tự bản ghi
                    Dim tmp As String
                    tmp = CStr(i + 1)
                    If d1 + 1 = 67 + 68 * (iTrang) Then
                        iCuoi = 1 ' duong bao phai bao ca doan trong
                    End If

                    If d = 67 + 68 * (iTrang - 1) Then
                        'cuoi trang thi in sang dau trang tiep
                        iCuoi2 = 2
                    End If

                    .Range(.Cells(d1, "B"), .Cells(d1, "D")).MergeCells = True
                    .Range(.Cells(d1, "E"), .Cells(d1, "F")).MergeCells = True
                    .Range(.Cells(d1, "I"), .Cells(d1, "L")).MergeCells = True
                    .Range(.Cells(d1, "M"), .Cells(d1, "P")).MergeCells = True

                    If tmp = 4 Then
                        tmp = tmp
                    End If
                    .Range(.Cells(d + iCuoi2, "A"), .Cells(d1 + iCuoi, "P")).Borders(&H9).Weight = &H1 '.BorderAround(, 2, -4105)
                    .Range(.Cells(d + iCuoi2, "A"), .Cells(d + iCuoi2, "A")).Value = tmp
                    .Range(.Cells(d + iCuoi2, "A"), .Cells(d + iCuoi2, "A")).Font.Size = 8.5
                    .Range(.Cells(d + iCuoi2, "A"), .Cells(d + iCuoi2, "A")).HorizontalAlignment = -4108
                    iCuoi = 0
                    iCuoi2 = 0

                    .Range(.Cells(d, "E"), .Cells(d, "F")).Value = MyTable.Rows(i).Item("SoPhatHanhGCN").ToString()

                    Dim temp As String
                    If IsDBNull((MyTable.Rows(i).Item("NgayKyGCN"))) Then
                        temp = ""
                    Else
                        temp = MyTable.Rows(i).Item("NgayKyGCN").date
                    End If

                    .Range(.Cells(d, "G"), .Cells(d, "G")).Value = temp

                    If IsDBNull(MyTable.Rows(i).Item("NgayTraGCN")) Then
                        temp = ""
                    Else
                        temp = MyTable.Rows(i).Item("NgayTraGCN").date
                    End If

                    .Range(.Cells(d, "H"), .Cells(d, "H")).Value = temp
                    If IsDBNull((MyTable.Rows(i).Item("NguoiKyGCN"))) Then
                        temp = ""
                    Else
                        temp = MyTable.Rows(i).Item("NguoiKyGCN").ToString
                    End If

                    .Range(.Cells(d, "i"), .Cells(d, "i")).Value = "" 'temp
                    If IsDBNull((MyTable.Rows(i).Item("GhiChuGCN"))) Then
                        temp = ""
                    Else
                        temp = MyTable.Rows(i).Item("GhiChuGCN").ToString
                    End If

                    .Range(.Cells(d, "M"), .Cells(d, "P")).Value = temp
                End With

                d1 += 1 ' tang 1 hang duoi moi ho so (nhom chu so huu)
            End If
        Next 'i
        da.Dispose()
        ds.Dispose()
        dss.Dispose()
        dst.Dispose()
        cmd.Dispose()

    End Sub
    Public Sub TieuDeChungCacTrangSoCapGCN(ByVal app As Object, ByVal tongdong As Long)
        With app

            Dim dong As Integer
            Dim i As Integer = 0
            While i * 68 + 68 < tongdong
                dong = 68
                If i = 0 Then
                    dong += 2
                End If

                .Range(.Cells(dong + 68 * i, "A"), .Cells(dong + 68 * i, "A")).Font.Italic = False
                .Range(.Cells(dong + 68 * i, "A"), .Cells(dong + 68 * i, "A")).Value = "1"
                .Range(.Cells(dong + 68 * i, "A"), .Cells(dong + 68 * i, "A")).BorderAround(, 2, -4105)
                .Range(.Cells(dong + 68 * i, "A"), .Cells(dong + 68 * i, "A")).HorizontalAlignment = -4108
                '-----------------------------------
                .Range(.Cells(dong + 68 * i, "B"), .Cells(dong + 68 * i, "D")).MergeCells = True
                .Range(.Cells(dong + 68 * i, "B"), .Cells(dong + 68 * i, "D")).Value = "2"
                .Range(.Cells(dong + 68 * i, "B"), .Cells(dong + 68 * i, "D")).HorizontalAlignment = -4108
                '------------------------------------
                .Range(.Cells(dong + 68 * i, "E"), .Cells(dong + 68 * i, "F")).MergeCells = True
                .Range(.Cells(dong + 68 * i, "E"), .Cells(dong + 68 * i, "F")).Value = "3"
                .Range(.Cells(dong + 68 * i, "E"), .Cells(dong + 68 * i, "F")).HorizontalAlignment = -4108
                '------------------------------------
                .Range(.Cells(dong + 68 * i, "G"), .Cells(dong + 68 * i, "G")).Value = "4"
                .Range(.Cells(dong + 68 * i, "G"), .Cells(dong + 68 * i, "G")).HorizontalAlignment = -4108
                '------------------------------------
                .Range(.Cells(dong + 68 * i, "H"), .Cells(dong + 68 * i, "H")).Value = "5"
                .Range(.Cells(dong + 68 * i, "H"), .Cells(dong + 68 * i, "H")).HorizontalAlignment = -4108
                '------------------------------------
                .Range(.Cells(dong + 68 * i, "I"), .Cells(dong + 68 * i, "L")).MergeCells = True
                .Range(.Cells(dong + 68 * i, "I"), .Cells(dong + 68 * i, "L")).Value = "6"
                .Range(.Cells(dong + 68 * i, "I"), .Cells(dong + 68 * i, "L")).HorizontalAlignment = -4108
                '------------------------------------
                .Range(.Cells(dong + 68 * i, "M"), .Cells(dong + 68 * i, "P")).MergeCells = True
                .Range(.Cells(dong + 68 * i, "M"), .Cells(dong + 68 * i, "P")).Value = "7"
                .Range(.Cells(dong + 68 * i, "M"), .Cells(dong + 68 * i, "P")).HorizontalAlignment = -4108
                ''------------ke cot
                .Range(.Cells(dong + 68 * i, "A"), .Cells(135 + 68 * i, "A")).BorderAround(, 2, -4105)
                .Range(.Cells(dong + 68 * i, "B"), .Cells(135 + 68 * i, "D")).BorderAround(, 2, -4105)
                .Range(.Cells(dong + 68 * i, "E"), .Cells(135 + 68 * i, "F")).BorderAround(, 2, -4105)
                .Range(.Cells(dong + 68 * i, "G"), .Cells(135 + 68 * i, "G")).BorderAround(, 2, -4105)
                .Range(.Cells(dong + 68 * i, "H"), .Cells(135 + 68 * i, "H")).BorderAround(, 2, -4105)
                .Range(.Cells(dong + 68 * i, "I"), .Cells(135 + 68 * i, "L")).BorderAround(, 2, -4105)
                .Range(.Cells(dong + 68 * i, "M"), .Cells(135 + 68 * i, "P")).BorderAround(, 2, -4105)

                .Range(.Cells(dong + 68 * i, "A"), .Cells(dong + 68 * i, "P")).BorderAround(, 2, -4105) '  duong ke ngang cua cot so
                .Range(.Cells(dong + 68 * i, "E"), .Cells(dong + 68 + 68 * i, "H")).HorizontalAlignment = -4108
                .Range(.Cells(dong + 68 * i, "A"), .Cells(dong + 68 + 68 * i, "P")).Font.Italic = True
                i += 1
            End While
        End With
    End Sub
#End Region
#Region "SoMucKe"
    Public Sub TrangMoDauSoMucKe(ByVal app As Object, ByVal ds As DataTable, ByVal sToBanDoSo As String, ByVal DonViHanhChinh() As String)

        Dim tinh As String = DonViHanhChinh(0)
        Dim huyen As String = DonViHanhChinh(1)
        Dim xa As String = DonViHanhChinh(2)
        Dim matinh01 As Integer = 0
        Dim matinh02 As Integer = 1

        Dim mahuyen01 As Integer = 0
        Dim mahuyen02 As Integer = 0
        Dim mahuyen03 As Integer = 1

        Dim maxa01 As Integer = 0
        Dim maxa02 As Integer = 0
        Dim maxa03 As Integer = 0
        Dim maxa04 As Integer = 2
        Dim maxa05 As Integer = 8
        Dim soquyen01 As String = 0
        Dim soquyen02 As String = 1
        Dim soquyen03 As String = 0

        Dim ngay As String = CStr(Now.Day)
        If Len(ngay) = 1 Then ngay = "0" + ngay

        Dim thang As String = CStr(Now.Month)
        If Len(thang) = 1 Then thang = "0" + thang

        With app

            .Cells(1, "B").ColumnWidth = 7.43
            .Cells(1, "C").ColumnWidth = 7.43
            .Cells(1, "M").ColumnWidth = 4.5
            .Cells(1, "N").ColumnWidth = 4.5
            .Cells(1, "O").ColumnWidth = 4.5
            .Cells(1, "P").ColumnWidth = 4.5
            .Cells(1, "Q").ColumnWidth = 4.5
            .Cells(1, "R").ColumnWidth = 4.5

            'Ban hành kèm theo thông tư.......
            .Range(.Cells(1, "B"), .Cells(4, "G")).MergeCells = True
            .Range(.Cells(1, "B"), .Cells(4, "G")).HorizontalAlignment = -4108
            .Range(.Cells(1, "B"), .Cells(4, "G")).VerticalAlignment = -4108
            .Range(.Cells(1, "B"), .Cells(4, "G")).Font.Size = 13
            .Range(.Cells(1, "B"), .Cells(4, "G")).Value = "Ban hành kèm theo thông tư Số 09/2007/TT-BTNMT                                                                              Ngày 02/08/2007 của Bộ Tài nguyên và Môi trường                                                              hướng dẫn lập, chỉnh lý, quản lý hồ sơ địa chính"
            .Range(.Cells(1, "B"), .Cells(4, "G")).WrapText = True
            ' Cộng hòa xã hội chủ nghĩa việt nam...
            .Range(.Cells(1, "I"), .Cells(4, "R")).MergeCells = True
            .Range(.Cells(1, "I"), .Cells(4, "R")).HorizontalAlignment = -4108
            .Range(.Cells(1, "I"), .Cells(4, "R")).VerticalAlignment = -4108
            .Range(.Cells(1, "I"), .Cells(4, "R")).Font.Size = 14
            .Range(.Cells(1, "I"), .Cells(4, "R")).Value = "CỘNG HÒA XÃ HỘI CHỦ NGHĨA VIỆT NAM                              Độc Lập - Tự Do - Hạnh Phúc"
            .Range(.Cells(1, "I"), .Cells(4, "R")).WrapText = True
            .Range(.Cells(1, "I"), .Cells(4, "R")).Font.Bold = True
            ' Mẫu số..
            .Range(.Cells(1, "U"), .Cells(2, "W")).MergeCells = True
            .Range(.Cells(1, "U"), .Cells(2, "W")).HorizontalAlignment = -4108
            .Range(.Cells(1, "U"), .Cells(2, "W")).VerticalAlignment = -4108
            .Range(.Cells(1, "U"), .Cells(2, "W")).Font.Size = 14
            .Range(.Cells(1, "U"), .Cells(2, "W")).Value = "Mẫu số: 01/ĐK"
            .Range(.Cells(1, "U"), .Cells(2, "W")).Font.Bold = True
            'Tiêu đề
            .Range(.Cells(14, "D"), .Cells(20, "T")).MergeCells = True
            .Range(.Cells(14, "D"), .Cells(20, "T")).HorizontalAlignment = -4108
            .Range(.Cells(14, "D"), .Cells(20, "T")).VerticalAlignment = -4108
            .Range(.Cells(14, "D"), .Cells(20, "T")).Font.Size = 60
            .Range(.Cells(14, "D"), .Cells(20, "T")).Value = "SỔ MỤC KÊ ĐẤT ĐAI"
            .Range(.Cells(14, "D"), .Cells(20, "T")).WrapText = True

            .Range(.Cells(23, "D"), .Cells(28, "L")).VerticalAlignment = -4160
            .Range(.Cells(23, "E"), .Cells(28, "K")).HorizontalAlignment = -4131
            .Range(.Cells(23, "L"), .Cells(28, "L")).HorizontalAlignment = -4152

            .Range(.Cells(23, "D"), .Cells(32, "L")).Font.Size = 14
            .Range(.Cells(23, "N"), .Cells(29, "R")).Font.Size = 11
            .Range(.Cells(23, "N"), .Cells(29, "R")).HorizontalAlignment = -4108
            .Range(.Cells(23, "N"), .Cells(29, "R")).VerticalAlignment = -4108

            .Range(.Cells(23, "D"), .Cells(24, "E")).MergeCells = True
            .Range(.Cells(23, "D"), .Cells(24, "E")).Value = "TỈNH:"

            .Range(.Cells(23, "F"), .Cells(24, "K")).MergeCells = True
            .Range(.Cells(23, "F"), .Cells(24, "K")).Value = tinh

            .Range(.Cells(23, "L"), .Cells(24, "L")).MergeCells = True
            .Range(.Cells(23, "L"), .Cells(24, "L")).Value = "Mã:"
            .Range(.Cells(23, "N"), .Cells(23, "N")).Value = matinh01
            .Range(.Cells(23, "O"), .Cells(23, "O")).Value = matinh02
            .Range(.Cells(23, "O"), .Cells(23, "O")).RowHeight = 18.75

            .Range(.Cells(23, "N"), .Cells(23, "N")).BorderAround(, 2, -4105)
            .Range(.Cells(23, "O"), .Cells(23, "O")).BorderAround(, 2, -4105)

            .Range(.Cells(25, "D"), .Cells(26, "E")).MergeCells = True
            .Range(.Cells(25, "D"), .Cells(26, "E")).Value = "HUYỆN:"
            .Range(.Cells(25, "F"), .Cells(26, "K")).MergeCells = True
            .Range(.Cells(25, "F"), .Cells(26, "K")).Value = huyen

            .Range(.Cells(25, "L"), .Cells(26, "L")).MergeCells = True
            .Range(.Cells(25, "L"), .Cells(26, "L")).Value = "Mã:"
            .Range(.Cells(25, "N"), .Cells(25, "N")).Value = mahuyen01
            .Range(.Cells(25, "O"), .Cells(25, "O")).Value = mahuyen02
            .Range(.Cells(25, "P"), .Cells(25, "P")).Value = mahuyen03
            .Range(.Cells(25, "P"), .Cells(25, "P")).RowHeight = 18.75

            .Range(.Cells(25, "N"), .Cells(25, "N")).BorderAround(, 2, -4105)
            .Range(.Cells(25, "O"), .Cells(25, "O")).BorderAround(, 2, -4105)
            .Range(.Cells(25, "P"), .Cells(25, "P")).BorderAround(, 2, -4105)

            .Range(.Cells(27, "D"), .Cells(28, "E")).MergeCells = True
            .Range(.Cells(27, "D"), .Cells(28, "E")).Value = "XÃ:"
            .Range(.Cells(27, "F"), .Cells(28, "K")).MergeCells = True
            .Range(.Cells(27, "F"), .Cells(28, "K")).Value = xa

            .Range(.Cells(27, "L"), .Cells(28, "L")).MergeCells = True
            .Range(.Cells(27, "L"), .Cells(28, "L")).Value = "Mã:"
            .Range(.Cells(27, "N"), .Cells(27, "N")).Value = maxa01
            .Range(.Cells(27, "O"), .Cells(27, "O")).Value = maxa02
            .Range(.Cells(27, "P"), .Cells(27, "P")).Value = maxa03
            .Range(.Cells(27, "Q"), .Cells(27, "Q")).Value = maxa04
            .Range(.Cells(27, "R"), .Cells(27, "R")).Value = maxa05
            .Range(.Cells(27, "R"), .Cells(27, "R")).RowHeight = 18.75

            .Range(.Cells(27, "N"), .Cells(27, "N")).BorderAround(, 2, -4105)
            .Range(.Cells(27, "O"), .Cells(27, "O")).BorderAround(, 2, -4105)
            .Range(.Cells(27, "P"), .Cells(27, "P")).BorderAround(, 2, -4105)
            .Range(.Cells(27, "Q"), .Cells(27, "Q")).BorderAround(, 2, -4105)
            .Range(.Cells(27, "R"), .Cells(27, "R")).BorderAround(, 2, -4105)

            .Range(.Cells(29, "K"), .Cells(30, "L")).MergeCells = True
            .Range(.Cells(29, "K"), .Cells(30, "L")).Value = "Quyển số:"
            .Range(.Cells(29, "K"), .Cells(30, "L")).HorizontalAlignment = -4152
            .Range(.Cells(29, "K"), .Cells(30, "L")).VerticalAlignment = -4160

            .Range(.Cells(29, "N"), .Cells(29, "N")).Value = soquyen01
            .Range(.Cells(29, "O"), .Cells(29, "O")).Value = soquyen02
            .Range(.Cells(29, "P"), .Cells(29, "P")).Value = soquyen03

            .Range(.Cells(29, "N"), .Cells(29, "N")).BorderAround(, 2, -4105)
            .Range(.Cells(29, "O"), .Cells(29, "O")).BorderAround(, 2, -4105)
            .Range(.Cells(29, "P"), .Cells(29, "P")).BorderAround(, 2, -4105)
            .Range(.Cells(29, "E"), .Cells(32, "J")).VerticalAlignment = -4160

            .Range(.Cells(31, "F"), .Cells(32, "I")).MergeCells = True
            .Range(.Cells(31, "F"), .Cells(32, "I")).Value = "Lập cho các tờ bản đồ số:"
            .Range(.Cells(31, "F"), .Cells(32, "R")).HorizontalAlignment = -4131
            .Range(.Cells(31, "J"), .Cells(32, "R")).MergeCells = True ' dong nay de ghi nhung so to ban do dc lap
            .Range(.Cells(31, "J"), .Cells(32, "R")).WrapText = True
            .Range(.Cells(31, "J"), .Cells(32, "R")).Value = sToBanDoSo

            .Range(.Cells(33, "D"), .Cells(39, "J")).MergeCells = True
            .Range(.Cells(33, "D"), .Cells(39, "J")).WrapText = True
            .Range(.Cells(33, "D"), .Cells(39, "J")).HorizontalAlignment = -4108
            .Range(.Cells(33, "D"), .Cells(39, "J")).VerticalAlignment = -4108
            .Range(.Cells(33, "D"), .Cells(39, "J")).Value = tinh & ", Ngày " & ngay & " tháng " & thang & " năm " & Now.Year & "                                              GIÁM ĐỐC                                                                                                    VĂN PHÒNG ĐĂNG KÝ QUYỀN SỬ DỤNG ĐẤT                                                        (Ký, Đóng dấu)"

            .Range(.Cells(33, "N"), .Cells(39, "U")).MergeCells = True
            .Range(.Cells(33, "N"), .Cells(39, "U")).WrapText = True
            .Range(.Cells(33, "N"), .Cells(39, "U")).HorizontalAlignment = -4108
            .Range(.Cells(33, "N"), .Cells(39, "U")).VerticalAlignment = -4108
            .Range(.Cells(33, "N"), .Cells(39, "U")).Value = tinh & ", Ngày " & ngay & " tháng " & thang & " năm " & Now.Year & "                                                  GIÁM ĐỐC                                                                                           SỞ TÀI NGUYÊN VÀ MÔI TRƯỜNG                                                 (Ký, Đóng dấu)"

            .Range(.Cells(33, "C"), .Cells(41, "U")).Font.Size = 13
            .Range(.Cells(10, "D"), .Cells(32, "L")).Font.Bold = True
        End With
    End Sub
    Public Sub TieuDeSoMucKe(ByVal app As Object)
        Try
            With app
                'Phan tieu de trang 1
                .Range(.Cells(48, "A"), .Cells(49, "W")).BorderAround(, 2, -4105)
                .Range(.Cells(48, "B"), .Cells(49, "D")).BorderAround(, 2, -4105)
                .Range(.Cells(48, "F"), .Cells(49, "F")).BorderAround(, 2, -4105)
                .Range(.Cells(48, "K"), .Cells(49, "W")).BorderAround(, 2, -4105)
                .Range(.Cells(48, "G"), .Cells(48, "W")).BorderAround(, 2, -4105)

                .Range(.Cells(49, "H"), .Cells(49, "H")).BorderAround(, 2, -4105)
                .Range(.Cells(49, "J"), .Cells(49, "J")).BorderAround(, 2, -4105)
                .Range(.Cells(49, "L"), .Cells(49, "Q")).BorderAround(, 2, -4105)
                .Range(.Cells(49, "T"), .Cells(49, "W")).BorderAround(, 2, -4105)

                .Range(.Cells(48, "A"), .Cells(49, "W")).HorizontalAlignment = -4108
                .Range(.Cells(48, "A"), .Cells(49, "W")).VerticalAlignment = -4108
                .Range(.Cells(48, "A"), .Cells(49, "A")).MergeCells = True
                .Range(.Cells(48, "A"), .Cells(49, "A")).WrapText = True
                .Range(.Cells(48, "A"), .Cells(49, "A")).Value = "Số hiệu thửa đất"

                .Range(.Cells(48, "B"), .Cells(49, "D")).MergeCells = True
                .Range(.Cells(48, "B"), .Cells(49, "D")).WrapText = True
                .Range(.Cells(48, "B"), .Cells(49, "D")).Value = "Tên người sử dụng, quản lý"

                .Range(.Cells(48, "E"), .Cells(49, "E")).MergeCells = True
                .Range(.Cells(48, "E"), .Cells(49, "E")).WrapText = True
                .Range(.Cells(48, "E"), .Cells(49, "E")).Value = "Loại đối tượng"

                .Range(.Cells(48, "F"), .Cells(49, "F")).MergeCells = True
                .Range(.Cells(48, "F"), .Cells(49, "F")).WrapText = True
                .Range(.Cells(48, "F"), .Cells(49, "F")).Value = "Diện tích      (m2)"

                .Range(.Cells(48, "G"), .Cells(48, "J")).MergeCells = True
                .Range(.Cells(48, "G"), .Cells(48, "J")).Value = "Mục đích sử dụng"

                .Range(.Cells(48, "K"), .Cells(48, "W")).MergeCells = True
                .Range(.Cells(48, "K"), .Cells(48, "W")).Value = "Ghi chú"

                .Range(.Cells(49, "L"), .Cells(49, "Q")).MergeCells = True
                .Range(.Cells(49, "L"), .Cells(49, "Q")).Value = "Nội dung thay đổi"

                .Range(.Cells(49, "T"), .Cells(49, "W")).MergeCells = True
                .Range(.Cells(49, "T"), .Cells(49, "W")).Value = "Nội dung thay đổi"

                .Range(.Cells(49, "G"), .Cells(49, "G")).Value = "Cấp GCN"
                .Range(.Cells(49, "H"), .Cells(49, "H")).Value = "Quy hoạch"
                .Range(.Cells(49, "I"), .Cells(49, "I")).Value = "Kiểm kê"
                .Range(.Cells(49, "J"), .Cells(49, "J")).Value = "Chi tiết"
                .Range(.Cells(49, "K"), .Cells(49, "K")).Value = "STT Thửa"

                .Range(.Cells(49, "R"), .Cells(49, "S")).MergeCells = True
                .Range(.Cells(49, "R"), .Cells(49, "S")).Value = "STT Thửa"

                .Range(.Cells(48, "A"), .Cells(49, "J")).Font.Bold = True
                .Range(.Cells(48, "K"), .Cells(48, "W")).Font.Bold = True
                .Range(.Cells(49, "K"), .Cells(49, "W")).Font.Bold = True
                .Range(.Cells(48, "A"), .Cells(49, "W")).Font.Size = 10
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub
    Public Sub ExportSoMucKe(ByVal app As Object, ByVal ds As DataTable, ByVal NamIn As Int16, ByVal DonViHanhChinh() As String)
        Dim iTrang As Integer = 1 ' dang in trang 1, trang mo dau la trang 0
        Dim iDongHienThoi As Integer = 50
        Dim iTam As Integer = 0
        Dim j As Integer
        Dim sNewTen, sOldTen, sNewSoThua, sOldSoThua, sNewKyHieu, sOldKyHieu As String
        Dim bKtra As Boolean = False

        sNewTen = "" : sOldTen = "" : sNewSoThua = "" : sOldSoThua = "" : sNewKyHieu = "" : sOldKyHieu = ""

        With app

            For i As Integer = 0 To ds.Rows.Count - 1

                Dim MyTable As New DataTable
                Dim paras() As String = {"@Flag", "@ToBanDo", "@NamIn", "@MaDVHC"}
                Dim Values() As String = {2, ds.Rows(i).Item("ToBanDo").ToString, NamIn, strDonViHangChinh}
                Dim MyTable1 As New DataTable
                MyTable1 = GetData_SP("sp_InSoMucKe", Values, paras)

                Dim arrSoThua() As String
                ReDim arrSoThua(0)

                For j = 0 To MyTable1.Rows.Count - 1
                    ReDim Preserve arrSoThua(j)
                    arrSoThua(j) = MyTable1.Rows(j).Item(0)

                Next


                For j = 0 To UBound(arrSoThua)

                    Dim paras2() As String = {"@Flag", "@SoThua", "@ToBanDo", "@NamIn"}
                    Dim Values2() As String = {3, arrSoThua(j), ds.Rows(i).Item("ToBanDo").ToString, NamIn}
                    Dim MyTableChuSD As New DataTable
                    MyTableChuSD = GetData_SP("sp_InSoMucKe", Values2, paras2)


                    If MyTableChuSD.Rows.Count > 0 Then

                        bKtra = True

                        iDem += 1
                        If iDem = 1 Then
                            sToBanDo = ds.Rows(i).Item("ToBanDo").ToString
                        Else
                            sToBanDo += ", " & ds.Rows(i).Item("ToBanDo").ToString
                        End If


                        KeTrangSoMucKe(i, iDongHienThoi, app, iTrang, ds)

                        Dim paras3() As String = {"@Flag", "@SoThua", "@ToBanDo", "@NamIn"}
                        Dim Values3() As String = {4, arrSoThua(j), ds.Rows(i).Item("ToBanDo").ToString, NamIn}
                        Dim MyTableMucDicSD As New DataTable
                        MyTableMucDicSD = GetData_SP("sp_InSoMucKe", Values3, paras3)


                        If MyTableChuSD.Rows.Count >= MyTableMucDicSD.Rows.Count Then

                            For t As Integer = 0 To MyTableChuSD.Rows.Count - 1
                                iDongHienThoi += 1
                                If iDongHienThoi = 46 + 48 * iTrang Then ' in den dong cuoi thi sang trang va in vao dong dau tien
                                    iTrang += 1
                                    KeThemTrangSoMucKe(iDongHienThoi, app, iTrang) ' ke them trang thua
                                    iDongHienThoi += 2
                                End If



                                If t <> 0 Then
                                    sOldSoThua = sNewSoThua
                                    'sOldTen = sNewTen
                                    'sOldKyHieu = sNewKyHieu
                                End If

                                sNewSoThua = MyTable1.Rows(j).Item("SoThua").ToString
                                If sNewSoThua <> sOldSoThua Then
                                    Dim tam As Integer
                                    If iTrang = 1 Then
                                        tam = 3
                                    Else
                                        tam = 1
                                    End If
                                    If iDongHienThoi <> tam + 48 * iTrang Then
                                        .Range(.Cells(iDongHienThoi, "A"), .Cells(iDongHienThoi, "J")).Borders(&H8).Weight = &H1
                                    End If

                                    .Range(.Cells(iDongHienThoi, "A"), .Cells(iDongHienThoi, "A")).Value = MyTable1.Rows(j).Item("SoThua").ToString
                                Else
                                    .Range(.Cells(iDongHienThoi, "A"), .Cells(iDongHienThoi, "A")).Value = "-"
                                End If

                                .Range(.Cells(iDongHienThoi, "B"), .Cells(iDongHienThoi, "D")).MergeCells = True
                                .Range(.Cells(iDongHienThoi, "B"), .Cells(iDongHienThoi, "D")).Value = " " & MyTableChuSD.Rows(t).Item("HoTen").ToString
                                .Range(.Cells(iDongHienThoi, "E"), .Cells(iDongHienThoi, "E")).Value = " " & MyTableChuSD.Rows(t).Item("DoiTuongSDD").ToString



                                If t < MyTableMucDicSD.Rows.Count Then
                                    .Range(.Cells(iDongHienThoi, "G"), .Cells(iDongHienThoi, "G")).Value = MyTableMucDicSD.Rows(t).Item("KyHieu").ToString
                                    .Range(.Cells(iDongHienThoi, "F"), .Cells(iDongHienThoi, "F")).Value = MyTableMucDicSD.Rows(t).Item("DienTichKeKhai").ToString
                                    .Range(.Cells(iDongHienThoi, "I"), .Cells(iDongHienThoi, "I")).Value = MyTableMucDicSD.Rows(t).Item("KyHieuKiemKe").ToString
                                    .Range(.Cells(iDongHienThoi, "H"), .Cells(iDongHienThoi, "H")).Value = MyTableMucDicSD.Rows(t).Item("KyHieuQuiHoach").ToString
                                    .Range(.Cells(iDongHienThoi, "J"), .Cells(iDongHienThoi, "J")).Value = MyTableMucDicSD.Rows(t).Item("ChiTiet").ToString
                                End If

                            Next

                        End If
                    End If
                    If bKtra = True Then

                        If iDongHienThoi < 46 + 48 * iTrang Then
                            iDongHienThoi = 46 + 48 * iTrang + 2
                        End If
                        ' het 1 iTrang
                        bKtra = False
                        iTrang += 1
                    End If
                Next



            Next
        End With

        TrangMoDauSoMucKe(app, ds, sToBanDo, DonViHanhChinh)

    End Sub
    Public Sub KeTrangSoMucKe(ByVal i As Integer, ByVal iDongHienThoi As Long, ByVal app As Object, ByVal iTrang As Integer, ByVal tab As DataTable)

        Dim iTam As Byte

        TangI += 1
        If TangI = 1 Then
            iTam = 2
        Else
            iTam = 0
        End If
        Try
            With app
                .Range(.Cells(iDongHienThoi - (1 + iTam), "A"), .Cells(iDongHienThoi - (1 + iTam), "A")).Font.Bold = True
                .Range(.Cells(iDongHienThoi - (1 + iTam), "A"), .Cells(iDongHienThoi - (1 + iTam), "A")).Font.Size = 11
                .Range(.Cells(iDongHienThoi - (1 + iTam), "A"), .Cells(iDongHienThoi - (1 + iTam), "A")).Value = "Số hiệu tờ bản đồ: " & tab.Rows(i).Item("ToBanDo").ToString

                .Range(.Cells(iDongHienThoi, "A"), .Cells(iDongHienThoi, "A")).BorderAround(, 2, -4105)
                .Range(.Cells(iDongHienThoi, "E"), .Cells(iDongHienThoi, "E")).BorderAround(, 2, -4105)
                .Range(.Cells(iDongHienThoi, "G"), .Cells(iDongHienThoi, "G")).BorderAround(, 2, -4105)
                .Range(.Cells(iDongHienThoi, "I"), .Cells(iDongHienThoi, "I")).BorderAround(, 2, -4105)
                .Range(.Cells(iDongHienThoi, "K"), .Cells(iDongHienThoi, "K")).BorderAround(, 2, -4105)
                .Range(.Cells(iDongHienThoi, "R"), .Cells(iDongHienThoi, "S")).MergeCells = True
                .Range(.Cells(iDongHienThoi, "R"), .Cells(iDongHienThoi, "S")).BorderAround(, 2, -4105)
                .Range(.Cells(iDongHienThoi, "T"), .Cells(iDongHienThoi, "W")).MergeCells = True
                .Range(.Cells(iDongHienThoi, "B"), .Cells(iDongHienThoi, "W")).BorderAround(, 2, -4105)

                .Range(.Cells(iDongHienThoi, "B"), .Cells(iDongHienThoi, "D")).MergeCells = True
                .Range(.Cells(iDongHienThoi, "L"), .Cells(iDongHienThoi, "Q")).MergeCells = True

                .Range(.Cells(iDongHienThoi, "A"), .Cells(iDongHienThoi, "W")).HorizontalAlignment = -4108

                .Range(.Cells(iDongHienThoi, "A"), .Cells(iDongHienThoi, "W")).NumberFormat = "(0)"
                .Range(.Cells(iDongHienThoi, "A"), .Cells(iDongHienThoi, "A")).Value = "1"
                .Range(.Cells(iDongHienThoi, "B"), .Cells(iDongHienThoi, "D")).Value = "2"
                .Range(.Cells(iDongHienThoi, "E"), .Cells(iDongHienThoi, "E")).Value = "3"
                .Range(.Cells(iDongHienThoi, "F"), .Cells(iDongHienThoi, "F")).Value = "4"
                .Range(.Cells(iDongHienThoi, "G"), .Cells(iDongHienThoi, "G")).Value = "5"
                .Range(.Cells(iDongHienThoi, "H"), .Cells(iDongHienThoi, "H")).Value = "6"
                .Range(.Cells(iDongHienThoi, "I"), .Cells(iDongHienThoi, "I")).Value = "7"
                .Range(.Cells(iDongHienThoi, "J"), .Cells(iDongHienThoi, "J")).Value = "8"
                .Range(.Cells(iDongHienThoi, "K"), .Cells(iDongHienThoi, "K")).Value = "9"
                .Range(.Cells(iDongHienThoi, "L"), .Cells(iDongHienThoi, "Q")).Value = "10"
                .Range(.Cells(iDongHienThoi, "R"), .Cells(iDongHienThoi, "S")).Value = "11"
                .Range(.Cells(iDongHienThoi, "T"), .Cells(iDongHienThoi, "W")).Value = "12"

                .Range(.Cells(iDongHienThoi + 1, "A"), .Cells(94 + 48 * (iTrang - 1), "A")).BorderAround(, 2, -4105)
                .Range(.Cells(iDongHienThoi + 1, "B"), .Cells(94 + 48 * (iTrang - 1), "D")).BorderAround(, 2, -4105)
                .Range(.Cells(iDongHienThoi + 1, "E"), .Cells(94 + 48 * (iTrang - 1), "E")).BorderAround(, 2, -4105)
                .Range(.Cells(iDongHienThoi + 1, "F"), .Cells(94 + 48 * (iTrang - 1), "F")).BorderAround(, 2, -4105)
                .Range(.Cells(iDongHienThoi + 1, "G"), .Cells(94 + 48 * (iTrang - 1), "G")).BorderAround(, 2, -4105)
                .Range(.Cells(iDongHienThoi + 1, "H"), .Cells(94 + 48 * (iTrang - 1), "H")).BorderAround(, 2, -4105)
                .Range(.Cells(iDongHienThoi + 1, "I"), .Cells(94 + 48 * (iTrang - 1), "I")).BorderAround(, 2, -4105)
                .Range(.Cells(iDongHienThoi + 1, "J"), .Cells(94 + 48 * (iTrang - 1), "J")).BorderAround(, 2, -4105)
                .Range(.Cells(iDongHienThoi + 1, "K"), .Cells(94 + 48 * (iTrang - 1), "K")).BorderAround(, 2, -4105)
                .Range(.Cells(iDongHienThoi + 1, "L"), .Cells(94 + 48 * (iTrang - 1), "Q")).BorderAround(, 2, -4105)
                .Range(.Cells(iDongHienThoi + 1, "R"), .Cells(94 + 48 * (iTrang - 1), "S")).BorderAround(, 2, -4105)
                .Range(.Cells(iDongHienThoi + 1, "T"), .Cells(94 + 48 * (iTrang - 1), "W")).BorderAround(, 2, -4105)
                .Range(.Cells(iDongHienThoi + 1, "A"), .Cells(94 + 48 * (iTrang - 1), "W")).BorderAround(, 2, -4105)

                .Range(.Cells(iDongHienThoi + 1, "A"), .Cells(94 + 48 * (iTrang - 1), "A")).Font.Italic = True
                .Range(.Cells(iDongHienThoi + 1, "B"), .Cells(94 + 48 * (iTrang - 1), "D")).Font.Italic = True
                .Range(.Cells(iDongHienThoi + 1, "F"), .Cells(94 + 48 * (iTrang - 1), "F")).Font.Italic = True
                .Range(.Cells(iDongHienThoi + 1, "J"), .Cells(94 + 48 * (iTrang - 1), "W")).Font.Italic = True
                .Range(.Cells(iDongHienThoi + 1, "E"), .Cells(94 + 48 * (iTrang - 1), "E")).HorizontalAlignment = -4108
                .Range(.Cells(iDongHienThoi + 1, "G"), .Cells(94 + 48 * (iTrang - 1), "G")).HorizontalAlignment = -4108
                .Range(.Cells(iDongHienThoi + 1, "H"), .Cells(94 + 48 * (iTrang - 1), "H")).HorizontalAlignment = -4108
                .Range(.Cells(iDongHienThoi + 1, "I"), .Cells(94 + 48 * (iTrang - 1), "I")).HorizontalAlignment = -4108
                .Range(.Cells(iDongHienThoi + 1, "A"), .Cells(94 + 48 * (iTrang - 1), "A")).HorizontalAlignment = -4108
                .Range(.Cells(iDongHienThoi + 1, "F"), .Cells(94 + 48 * (iTrang - 1), "F")).HorizontalAlignment = -4152

            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub
    Public Sub KeThemTrangSoMucKe(ByVal iDongHienThoi As Long, ByVal app As Object, ByVal iTrang As Integer)
        With app

            .Range(.Cells(iDongHienThoi + 1, "A"), .Cells(iDongHienThoi + 1, "A")).BorderAround(, 2, -4105)
            .Range(.Cells(iDongHienThoi + 1, "E"), .Cells(iDongHienThoi + 1, "E")).BorderAround(, 2, -4105)
            .Range(.Cells(iDongHienThoi + 1, "G"), .Cells(iDongHienThoi + 1, "G")).BorderAround(, 2, -4105)
            .Range(.Cells(iDongHienThoi + 1, "I"), .Cells(iDongHienThoi + 1, "I")).BorderAround(, 2, -4105)
            .Range(.Cells(iDongHienThoi + 1, "K"), .Cells(iDongHienThoi + 1, "K")).BorderAround(, 2, -4105)
            .Range(.Cells(iDongHienThoi + 1, "R"), .Cells(iDongHienThoi + 1, "S")).MergeCells = True
            .Range(.Cells(iDongHienThoi + 1, "R"), .Cells(iDongHienThoi + 1, "S")).BorderAround(, 2, -4105)
            .Range(.Cells(iDongHienThoi + 1, "T"), .Cells(iDongHienThoi + 1, "W")).MergeCells = True
            .Range(.Cells(iDongHienThoi + 1, "B"), .Cells(iDongHienThoi + 1, "W")).BorderAround(, 2, -4105)

            .Range(.Cells(iDongHienThoi + 1, "B"), .Cells(iDongHienThoi + 1, "D")).MergeCells = True
            .Range(.Cells(iDongHienThoi + 1, "L"), .Cells(iDongHienThoi + 1, "Q")).MergeCells = True

            .Range(.Cells(iDongHienThoi + 1, "A"), .Cells(iDongHienThoi + 1, "W")).HorizontalAlignment = -4108

            .Range(.Cells(iDongHienThoi + 1, "A"), .Cells(iDongHienThoi + 1, "W")).NumberFormat = "(0)"
            .Range(.Cells(iDongHienThoi + 1, "A"), .Cells(iDongHienThoi + 1, "A")).Value = "1"
            .Range(.Cells(iDongHienThoi + 1, "B"), .Cells(iDongHienThoi + 1, "D")).Value = "2"
            .Range(.Cells(iDongHienThoi + 1, "E"), .Cells(iDongHienThoi + 1, "E")).Value = "3"
            .Range(.Cells(iDongHienThoi + 1, "F"), .Cells(iDongHienThoi + 1, "F")).Value = "4"
            .Range(.Cells(iDongHienThoi + 1, "G"), .Cells(iDongHienThoi + 1, "G")).Value = "5"
            .Range(.Cells(iDongHienThoi + 1, "H"), .Cells(iDongHienThoi + 1, "H")).Value = "6"
            .Range(.Cells(iDongHienThoi + 1, "I"), .Cells(iDongHienThoi + 1, "I")).Value = "7"
            .Range(.Cells(iDongHienThoi + 1, "J"), .Cells(iDongHienThoi + 1, "J")).Value = "8"
            .Range(.Cells(iDongHienThoi + 1, "K"), .Cells(iDongHienThoi + 1, "K")).Value = "9"
            .Range(.Cells(iDongHienThoi + 1, "L"), .Cells(iDongHienThoi + 1, "Q")).Value = "10"
            .Range(.Cells(iDongHienThoi + 1, "R"), .Cells(iDongHienThoi + 1, "S")).Value = "11"
            .Range(.Cells(iDongHienThoi + 1, "T"), .Cells(iDongHienThoi + 1, "W")).Value = "12"

            .Range(.Cells(iDongHienThoi + 1, "A"), .Cells(94 + 48 * (iTrang - 1), "A")).BorderAround(, 2, -4105)
            .Range(.Cells(iDongHienThoi + 1, "B"), .Cells(94 + 48 * (iTrang - 1), "D")).BorderAround(, 2, -4105)
            .Range(.Cells(iDongHienThoi + 1, "E"), .Cells(94 + 48 * (iTrang - 1), "E")).BorderAround(, 2, -4105)
            .Range(.Cells(iDongHienThoi + 1, "F"), .Cells(94 + 48 * (iTrang - 1), "F")).BorderAround(, 2, -4105)
            .Range(.Cells(iDongHienThoi + 1, "G"), .Cells(94 + 48 * (iTrang - 1), "G")).BorderAround(, 2, -4105)
            .Range(.Cells(iDongHienThoi + 1, "H"), .Cells(94 + 48 * (iTrang - 1), "H")).BorderAround(, 2, -4105)
            .Range(.Cells(iDongHienThoi + 1, "I"), .Cells(94 + 48 * (iTrang - 1), "I")).BorderAround(, 2, -4105)
            .Range(.Cells(iDongHienThoi + 1, "J"), .Cells(94 + 48 * (iTrang - 1), "J")).BorderAround(, 2, -4105)
            .Range(.Cells(iDongHienThoi + 1, "K"), .Cells(94 + 48 * (iTrang - 1), "K")).BorderAround(, 2, -4105)
            .Range(.Cells(iDongHienThoi + 1, "L"), .Cells(94 + 48 * (iTrang - 1), "Q")).BorderAround(, 2, -4105)
            .Range(.Cells(iDongHienThoi + 1, "R"), .Cells(94 + 48 * (iTrang - 1), "S")).BorderAround(, 2, -4105)
            .Range(.Cells(iDongHienThoi + 1, "T"), .Cells(94 + 48 * (iTrang - 1), "W")).BorderAround(, 2, -4105)
            .Range(.Cells(iDongHienThoi + 1, "A"), .Cells(94 + 48 * (iTrang - 1), "W")).BorderAround(, 2, -4105)

            .Range(.Cells(iDongHienThoi + 2, "A"), .Cells(94 + 48 * (iTrang - 1), "A")).Font.Italic = True
            .Range(.Cells(iDongHienThoi + 2, "B"), .Cells(94 + 48 * (iTrang - 1), "D")).Font.Italic = True
            .Range(.Cells(iDongHienThoi + 2, "F"), .Cells(94 + 48 * (iTrang - 1), "F")).Font.Italic = True
            .Range(.Cells(iDongHienThoi + 2, "J"), .Cells(94 + 48 * (iTrang - 1), "W")).Font.Italic = True
            .Range(.Cells(iDongHienThoi + 1, "E"), .Cells(94 + 48 * (iTrang - 1), "E")).HorizontalAlignment = -4108
            .Range(.Cells(iDongHienThoi + 1, "G"), .Cells(94 + 48 * (iTrang - 1), "G")).HorizontalAlignment = -4108
            .Range(.Cells(iDongHienThoi + 1, "H"), .Cells(94 + 48 * (iTrang - 1), "H")).HorizontalAlignment = -4108
            .Range(.Cells(iDongHienThoi + 1, "I"), .Cells(94 + 48 * (iTrang - 1), "I")).HorizontalAlignment = -4108
            .Range(.Cells(iDongHienThoi + 1, "A"), .Cells(94 + 48 * (iTrang - 1), "A")).HorizontalAlignment = -4108
            .Range(.Cells(iDongHienThoi + 1, "F"), .Cells(94 + 48 * (iTrang - 1), "F")).HorizontalAlignment = -4152
        End With
    End Sub
#End Region
#Region "SoTheoDoiChinhLyBienDong"
    Public Sub TrangMoDauSoTheoDoiBienDong(ByVal wapp As Object, ByVal DonViHanhChinh() As String)
        Dim tinh As String = DonViHanhChinh(0)
        Dim huyen As String = DonViHanhChinh(1)
        Dim xa As String = DonViHanhChinh(2)
        Dim matinh01 As Integer = 0
        Dim matinh02 As Integer = 1

        Dim mahuyen01 As Integer = 0
        Dim mahuyen02 As Integer = 0
        Dim mahuyen03 As Integer = 1

        Dim maxa01 As Integer = 0
        Dim maxa02 As Integer = 0
        Dim maxa03 As Integer = 0
        Dim maxa04 As Integer = 2
        Dim maxa05 As Integer = 8
        Dim soquyen01 As String = 0
        Dim soquyen02 As String = 1
        Dim soquyen03 As String = 0

        Dim ngay As String = CStr(Now.Day)
        If Len(ngay) = 1 Then ngay = "0" + ngay

        Dim thang As String = CStr(Now.Month)
        If Len(thang) = 1 Then thang = "0" + thang


        With wapp

            .Cells(1, "A").ColumnWidth = 5.29
            .Cells(1, "B").ColumnWidth = 12.71
            .Cells(1, "E").ColumnWidth = 3.14
            .Cells(1, "G").ColumnWidth = 12.71
            .Cells(1, "H").ColumnWidth = 16.86
            .Cells(1, "I").ColumnWidth = 4.29
            .Cells(1, "J").ColumnWidth = 4.29
            .Cells(1, "K").ColumnWidth = 4.29
            .Cells(1, "L").ColumnWidth = 4.29
            .Cells(1, "M").ColumnWidth = 4.29
            .Cells(1, "N").ColumnWidth = 4.29
            .Cells(1, "O").ColumnWidth = 4.29
            .Cells(1, "P").ColumnWidth = 4.29

            .Range(.Cells(1, "A"), .Cells(38, "P")).VerticalAlignment = -4160
            .Range(.Cells(1, "A"), .Cells(24, "P")).HorizontalAlignment = -4108

            .Range(.Cells(31, "B"), .Cells(36, "G")).HorizontalAlignment = -4131
            .Range(.Cells(31, "H"), .Cells(38, "H")).HorizontalAlignment = -4152

            .Range(.Cells(1, "E"), .Cells(3, "P")).Font.Bold = True
            .Range(.Cells(18, "A"), .Cells(24, "P")).Font.Bold = True
            .Range(.Cells(31, "b"), .Cells(38, "H")).Font.Bold = True

            'Ban hành kèm theo thông tư.......

            'Ban hành kèm theo thông tư.......
            '.Range(.Cells(1, "B"), .Cells(4, "G")).MergeCells = True
            '.Range(.Cells(1, "B"), .Cells(4, "G")).HorizontalAlignment = -4108
            '.Range(.Cells(1, "B"), .Cells(4, "G")).VerticalAlignment = -4108
            '.Range(.Cells(1, "B"), .Cells(4, "G")).Font.Size = 13
            '.Range(.Cells(1, "B"), .Cells(4, "G")).Value = "Ban hành kèm theo thông tư Số 09/2007/TT-BTNMT                                                                              Ngày 02/08/2007 của Bộ Tài nguyên và Môi trường                                                              hướng dẫn lập, chỉnh lý, quản lý hồ sơ địa chính"
            '.Range(.Cells(1, "B"), .Cells(4, "G")).WrapText = True


            .Range(.Cells(1, "A"), .Cells(7, "E")).HorizontalAlignment = -4108
            .Range(.Cells(1, "A"), .Cells(7, "E")).VerticalAlignment = -4108
            .Range(.Cells(1, "A"), .Cells(7, "E")).MergeCells = True
            .Range(.Cells(1, "A"), .Cells(7, "E")).Font.Size = 13
            .Range(.Cells(1, "A"), .Cells(7, "E")).Value = "Ban hành kèm theo thông tư                                                  Số 09/2007/TT-BTNMT                                                                              Ngày 02/08/2007 của Bộ Tài nguyên                                                           và Môi trường hướng dẫn lập, chỉnh lý,                                            quản lý hồ sơ địa chính"
            .Range(.Cells(1, "A"), .Cells(7, "E")).WrapText = True
            ' Cộng hòa xã hội chủ nghĩa việt nam...
            .Range(.Cells(1, "F"), .Cells(3, "L")).MergeCells = True
            .Range(.Cells(1, "F"), .Cells(3, "L")).Font.Size = 14
            .Range(.Cells(1, "F"), .Cells(3, "L")).Value = "CỘNG HÒA XÃ HỘI CHỦ NGHĨA VIỆT NAM                              Độc Lập - Tự Do - Hạnh Phúc"
            .Range(.Cells(1, "F"), .Cells(3, "L")).WrapText = True
            ' Mẫu số..
            .Range(.Cells(1, "M"), .Cells(2, "P")).MergeCells = True
            .Range(.Cells(1, "M"), .Cells(2, "P")).Font.Size = 14
            .Range(.Cells(1, "M"), .Cells(2, "P")).Value = "Mẫu số: 03/ĐK"
            'Tiêu đề
            .Range(.Cells(18, "A"), .Cells(24, "P")).MergeCells = True
            .Range(.Cells(18, "A"), .Cells(24, "P")).Font.Size = 40
            .Range(.Cells(18, "A"), .Cells(24, "P")).Value = "SỔ                                                                   THEO DÕI BIẾN ĐỘNG ĐẤT ĐAI"
            .Range(.Cells(18, "A"), .Cells(24, "P")).WrapText = True

            .Range(.Cells(31, "B"), .Cells(38, "H")).Font.Size = 14
            .Range(.Cells(31, "J"), .Cells(38, "N")).Font.Size = 11
            .Range(.Cells(31, "J"), .Cells(38, "N")).HorizontalAlignment = -4108
            .Range(.Cells(31, "J"), .Cells(38, "N")).VerticalAlignment = -4108

            .Range(.Cells(31, "B"), .Cells(32, "C")).MergeCells = True
            .Range(.Cells(31, "B"), .Cells(32, "C")).Value = "TỈNH:"

            .Range(.Cells(31, "D"), .Cells(32, "G")).MergeCells = True
            .Range(.Cells(31, "D"), .Cells(32, "G")).Value = tinh

            .Range(.Cells(31, "H"), .Cells(32, "H")).MergeCells = True
            .Range(.Cells(31, "H"), .Cells(32, "H")).Value = "Mã:"
            .Range(.Cells(31, "J"), .Cells(31, "J")).Value = matinh01
            .Range(.Cells(31, "K"), .Cells(31, "K")).Value = matinh02

            .Range(.Cells(31, "J"), .Cells(31, "J")).BorderAround(, 2, -4105)
            .Range(.Cells(31, "K"), .Cells(31, "K")).BorderAround(, 2, -4105)

            .Range(.Cells(33, "B"), .Cells(34, "C")).MergeCells = True
            .Range(.Cells(33, "B"), .Cells(34, "C")).Value = "HUYỆN:"
            .Range(.Cells(33, "D"), .Cells(34, "G")).MergeCells = True
            .Range(.Cells(33, "D"), .Cells(34, "G")).Value = huyen

            .Range(.Cells(33, "H"), .Cells(34, "H")).MergeCells = True
            .Range(.Cells(33, "H"), .Cells(34, "H")).Value = "Mã:"
            .Range(.Cells(33, "J"), .Cells(33, "J")).Value = mahuyen01
            .Range(.Cells(33, "K"), .Cells(33, "K")).Value = mahuyen02
            .Range(.Cells(33, "L"), .Cells(33, "L")).Value = mahuyen03

            .Range(.Cells(33, "J"), .Cells(33, "J")).BorderAround(, 2, -4105)
            .Range(.Cells(33, "K"), .Cells(33, "K")).BorderAround(, 2, -4105)
            .Range(.Cells(33, "L"), .Cells(33, "L")).BorderAround(, 2, -4105)

            .Range(.Cells(35, "B"), .Cells(36, "C")).MergeCells = True
            .Range(.Cells(35, "B"), .Cells(36, "C")).Value = "XÃ:"
            .Range(.Cells(35, "D"), .Cells(36, "G")).MergeCells = True
            .Range(.Cells(35, "D"), .Cells(36, "G")).Value = xa

            .Range(.Cells(35, "H"), .Cells(36, "H")).MergeCells = True
            .Range(.Cells(35, "H"), .Cells(36, "H")).Value = "Mã:"
            .Range(.Cells(35, "J"), .Cells(35, "J")).Value = maxa01
            .Range(.Cells(35, "K"), .Cells(35, "K")).Value = maxa02
            .Range(.Cells(35, "L"), .Cells(35, "L")).Value = maxa03
            .Range(.Cells(35, "M"), .Cells(35, "M")).Value = maxa04
            .Range(.Cells(35, "N"), .Cells(35, "N")).Value = maxa05

            .Range(.Cells(35, "J"), .Cells(35, "J")).BorderAround(, 2, -4105)
            .Range(.Cells(35, "K"), .Cells(35, "K")).BorderAround(, 2, -4105)
            .Range(.Cells(35, "L"), .Cells(35, "L")).BorderAround(, 2, -4105)
            .Range(.Cells(35, "M"), .Cells(35, "M")).BorderAround(, 2, -4105)
            .Range(.Cells(35, "N"), .Cells(35, "N")).BorderAround(, 2, -4105)

            .Range(.Cells(37, "H"), .Cells(38, "H")).MergeCells = True
            .Range(.Cells(37, "H"), .Cells(38, "H")).Value = "Quyển số:"

            .Range(.Cells(37, "J"), .Cells(37, "J")).Value = soquyen01
            .Range(.Cells(37, "K"), .Cells(37, "K")).Value = soquyen02
            .Range(.Cells(37, "L"), .Cells(37, "L")).Value = soquyen03

            .Range(.Cells(37, "J"), .Cells(37, "J")).BorderAround(, 2, -4105)
            .Range(.Cells(37, "K"), .Cells(37, "K")).BorderAround(, 2, -4105)
            .Range(.Cells(37, "L"), .Cells(37, "L")).BorderAround(, 2, -4105)
        End With
    End Sub
    Public Sub KhungSoTheoDoiBDDD(ByVal app As Object, ByVal iSoNam As Integer, ByVal iSonguoi As Integer)

        With app
            .Range(.Cells(72, "A"), .Cells(75, "P")).BorderAround(, 2, -4105)
            .Range(.Cells(72, "B"), .Cells(75, "C")).BorderAround(, 2, -4105)
            .Range(.Cells(72, "F"), .Cells(72, "G")).BorderAround(, 2, -4105)
            .Range(.Cells(73, "F"), .Cells(75, "F")).BorderAround(, 2, -4105)
            .Range(.Cells(73, "G"), .Cells(75, "G")).BorderAround(, 2, -4105)
            .Range(.Cells(72, "A"), .Cells(75, "A")).MergeCells = True
            .Range(.Cells(72, "B"), .Cells(75, "C")).MergeCells = True
            .Range(.Cells(72, "D"), .Cells(75, "E")).MergeCells = True
            .Range(.Cells(72, "F"), .Cells(72, "G")).MergeCells = True
            .Range(.Cells(73, "F"), .Cells(75, "F")).MergeCells = True
            .Range(.Cells(73, "G"), .Cells(75, "G")).MergeCells = True
            .Range(.Cells(72, "H"), .Cells(75, "P")).MergeCells = True

            .Range(.Cells(72, "A"), .Cells(75, "P")).HorizontalAlignment = -4108
            .Range(.Cells(72, "A"), .Cells(75, "P")).VerticalAlignment = -4108
            .Range(.Cells(72, "A"), .Cells(75, "P")).WrapText = True
            .Range(.Cells(72, "A"), .Cells(75, "P")).Font.Bold = True

            .Range(.Cells(72, "A"), .Cells(75, "A")).Value = "Số     Thứ Tự"
            .Range(.Cells(72, "B"), .Cells(75, "C")).Value = "Tên và địa chỉ của người đăng ký           biến động"
            .Range(.Cells(72, "D"), .Cells(75, "E")).Value = "Thời điểm đăng ký           biến động"
            .Range(.Cells(72, "F"), .Cells(72, "G")).Value = "Thửa đất biến động"
            .Range(.Cells(73, "F"), .Cells(75, "F")).Value = "Tờ bản đồ số"
            .Range(.Cells(73, "G"), .Cells(75, "G")).Value = "Thửa đất          số"
            .Range(.Cells(72, "H"), .Cells(75, "P")).Value = "Nội dung biến động"

            .Range(.Cells(72, "A"), .Cells(72, "A")).RowHeight += 3
            .Range(.Cells(73, "A"), .Cells(73, "A")).RowHeight -= 1
            .Range(.Cells(74, "A"), .Cells(74, "A")).RowHeight -= 1
            .Range(.Cells(75, "A"), .Cells(75, "A")).RowHeight -= 1
            ''========================================================
            'phan chung giua cac trang

            Dim dong As Integer
            Dim iTrang As Integer = 0

            While iTrang * 68 < 3 * iSoNam + 4 * iSonguoi 'Convert.ToInt32(iSotrang)
                dong = 72

                If iTrang = 0 Then
                    dong += 4
                End If

                .Range(.Cells(dong + 68 * iTrang, "A"), .Cells(dong + 68 * iTrang, "A")).BorderAround(, 2, -4105)
                .Range(.Cells(dong + 68 * iTrang, "B"), .Cells(dong + 68 * iTrang, "C")).BorderAround(, 2, -4105)
                .Range(.Cells(dong + 68 * iTrang, "D"), .Cells(dong + 68 * iTrang, "E")).BorderAround(, 2, -4105)
                .Range(.Cells(dong + 68 * iTrang, "F"), .Cells(dong + 68 * iTrang, "F")).BorderAround(, 2, -4105)
                .Range(.Cells(dong + 68 * iTrang, "G"), .Cells(dong + 68 * iTrang, "G")).BorderAround(, 2, -4105)
                .Range(.Cells(dong + 68 * iTrang, "H"), .Cells(dong + 68 * iTrang, "P")).BorderAround(, 2, -4105)

                .Range(.Cells(dong + 68 * iTrang, "B"), .Cells(dong + 68 * iTrang, "C")).MergeCells = True
                .Range(.Cells(dong + 68 * iTrang, "D"), .Cells(dong + 68 * iTrang, "E")).MergeCells = True
                .Range(.Cells(dong + 68 * iTrang, "H"), .Cells(dong + 68 * iTrang, "P")).MergeCells = True
                .Range(.Cells(dong + 68 * iTrang, "A"), .Cells(dong + 68 * iTrang, "P")).NumberFormat = "(0)"
                .Range(.Cells(dong + 68 * iTrang, "A"), .Cells(dong + 68 * iTrang, "P")).HorizontalAlignment = -4108

                .Range(.Cells(dong + 68 * iTrang, "A"), .Cells(dong + 68 * iTrang, "A")).Value = "1"
                .Range(.Cells(dong + 68 * iTrang, "B"), .Cells(dong + 68 * iTrang, "C")).Value = "2"
                .Range(.Cells(dong + 68 * iTrang, "D"), .Cells(dong + 68 * iTrang, "E")).Value = "3"
                .Range(.Cells(dong + 68 * iTrang, "F"), .Cells(dong + 68 * iTrang, "F")).Value = "4"
                .Range(.Cells(dong + 68 * iTrang, "G"), .Cells(dong + 68 * iTrang, "G")).Value = "5"
                .Range(.Cells(dong + 68 * iTrang, "H"), .Cells(dong + 68 * iTrang, "P")).Value = "6"
                ' ''ket cac cot
                .Range(.Cells(dong + 68 * iTrang + 1, "A"), .Cells(139 + 68 * iTrang, "A")).BorderAround(, 2, -4105)
                .Range(.Cells(dong + 68 * iTrang + 1, "D"), .Cells(139 + 68 * iTrang, "E")).BorderAround(, 2, -4105)
                .Range(.Cells(dong + 68 * iTrang + 1, "G"), .Cells(139 + 68 * iTrang, "G")).BorderAround(, 2, -4105)
                .Range(.Cells(dong + 68 * iTrang + 1, "B"), .Cells(139 + 68 * iTrang, "P")).BorderAround(, 2, -4105)

                .Range(.Cells(dong + 68 * iTrang + 1, "A"), .Cells(139 + 68 * iTrang, "P")).NumberFormat = "@"

                .Range(.Cells(dong + 68 * iTrang + 1, "A"), .Cells(139 + 68 * iTrang, "A")).Font.Italic = True
                .Range(.Cells(dong + 68 * iTrang + 1, "D"), .Cells(139 + 68 * iTrang, "E")).Font.Italic = True
                .Range(.Cells(dong + 68 * iTrang + 1, "H"), .Cells(139 + 68 * iTrang, "P")).Font.Italic = True

                .Range(.Cells(dong + 68 * iTrang + 1, "A"), .Cells(139 + 68 * iTrang, "A")).HorizontalAlignment = -4108
                .Range(.Cells(dong + 68 * iTrang + 1, "B"), .Cells(139 + 68 * iTrang, "C")).HorizontalAlignment = -4108
                .Range(.Cells(dong + 68 * iTrang + 1, "D"), .Cells(139 + 68 * iTrang, "E")).HorizontalAlignment = -4108
                .Range(.Cells(dong + 68 * iTrang + 1, "F"), .Cells(139 + 68 * iTrang, "F")).HorizontalAlignment = -4108
                .Range(.Cells(dong + 68 * iTrang + 1, "G"), .Cells(139 + 68 * iTrang, "G")).HorizontalAlignment = -4108

                iTrang += 1
            End While

            .Range(.Cells(72, "A"), .Cells(139 + 68 * (iTrang - 1), "P")).Font.Size = 12 ' font chung ca toan trang
        End With
    End Sub
    Public Sub ExportSoTheoDoiBienDong(ByVal app As Object, ByVal nam As Array, ByVal NamIn As String, ByVal MaDonViHanhChinh As String)
        Dim iTrang As Integer = 1 ' dang in trang 1
        Dim iDau As Integer = 71 + 6
        Dim j As Integer
        Dim sTam As String = ""
        Dim iDongHienThoi As Integer ' = 76
        Dim iKhoangtang As Integer = 0
        Dim iKhoangtangi As Integer = 0
        Dim iDautruoc As Integer
        conn = New SqlConnection(strConnection)
        cmd.Connection = conn
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT * FROM tblDangKyBienDong" ' lay tong so nguoi * 4 + 3 sau do nhan voi so nam
        'cmd.ExecuteNonQuery()
        da.SelectCommand = cmd
        Dim dss As New DataSet
        dss.Tables.Clear()
        da.Fill(dss, "Tong")

        KhungSoTheoDoiBDDD(app, UBound(nam) + 1, dss.Tables("Tong").Rows.Count)

        With app
            For i As Integer = 0 To UBound(nam)
                If NamIn = nam(i) Then


                    iDautruoc = iDau

                    If iDau + 6 > iTrang * 68 + 71 Then
                        iDau = (iTrang * 68 + 71) + 2 ' chuyen diem du den dau trang toi
                        iTrang += 1
                    End If

                    .Range(.Cells(iDau - 1, "A"), .Cells(iDautruoc - (4 * j + 3) - iKhoangtang, "P")).BorderAround(, 2, -4105)

                    iKhoangtang = 0

                    .Range(.Cells(iDau, "B"), .Cells(iDau + 1, "C")).MergeCells = True
                    .Range(.Cells(iDau, "B"), .Cells(iDau + 1, "C")).BorderAround(, 2, -4105)
                    .Range(.Cells(iDau, "B"), .Cells(iDau + 1, "C")).Font.Size = 16
                    .Range(.Cells(iDau, "B"), .Cells(iDau + 1, "C")).WrapText = True
                    .Range(.Cells(iDau, "B"), .Cells(iDau + 1, "C")).Font.Bold = True
                    .Range(.Cells(iDau, "B"), .Cells(iDau + 1, "C")).HorizontalAlignment = -4108
                    .Range(.Cells(iDau, "B"), .Cells(iDau + 1, "C")).VerticalAlignment = -4108
                    .Range(.Cells(iDau, "B"), .Cells(iDau + 1, "C")).Value = "Năm " & NamIn

                    iDongHienThoi = iDau + 3
                    cmd.Connection = conn
                    cmd.CommandType = CommandType.Text

                    cmd.CommandText = "SELECT DISTINCT tblDangKyBienDong.TenNguoiDangKy, tblDangKyBienDong.DiaChiNguoiDangKy,tblDangKyBienDong.ThoiDiemDangKy, tblThuaDatCapGCN.ToBanDo, tblThuaDatCapGCN.SoThua, tblDangKyBienDong.NoidungSoBienDong,tblTuDienDonViHanhChinh.MaDonViHanhChinh" & _
                                        " FROM tblDangKyBienDong INNER JOIN tblHoSoCapGCN ON tblHoSoCapGCN.MaHoSoCapGCN = tblDangKyBienDong.MaHoSoCapGCN " & _
                                       " INNER JOIN tblThuaDatCapGCN ON tblDangKyBienDong.MaHoSoCapGCN = tblThuaDatCapGCN.MaHoSoCapGCN " & _
                                       " INNER JOIN tblTuDienDonViHanhChinh ON tblTuDienDonViHanhChinh.MaXa=tblHoSoCapGCN.MaXa       " & _
                                       " WHERE tblTuDienDonViHanhChinh.MaDonViHanhChinh='" + MaDonViHanhChinh + "' AND year(tblDangKyBienDong.ThoiDiemDangKy)='" + NamIn + "'  ORDER BY tblDangKyBienDong.ThoiDiemDangKy"
                    'cmd.ExecuteNonQuery()
                    da.SelectCommand = cmd
                    dss.Tables.Clear()
                    da.Fill(dss, "NguoiDangKyBienDong")


                    For k As Integer = 0 To dss.Tables("NguoiDangKyBienDong").Rows.Count * 4
                        .Range(.Cells(iDongHienThoi + k - 3, "H"), .Cells(iDongHienThoi + k - 3, "P")).MergeCells = True
                    Next

                    For j = 0 To dss.Tables("NguoiDangKyBienDong").Rows.Count - 1

                        If iDongHienThoi + 3 > iTrang * 68 + 71 Then
                            iKhoangtang = (iTrang * 68 + 71) - iDongHienThoi + 2
                            iDongHienThoi += iKhoangtang
                            iTrang += 1
                        End If

                        sTam = ""
                        If j + 1 < 10 Then
                            sTam = "0"
                        End If
                        sTam += CStr(j + 1)

                        .Range(.Cells(iDongHienThoi, "A"), .Cells(iDongHienThoi, "A")).Value = sTam

                        .Range(.Cells(iDongHienThoi - 1, "B"), .Cells(iDongHienThoi - 1, "C")).MergeCells = True
                        .Range(.Cells(iDongHienThoi, "B"), .Cells(iDongHienThoi, "C")).MergeCells = True
                        .Range(.Cells(iDongHienThoi, "B"), .Cells(iDongHienThoi, "C")).Value = dss.Tables("NguoiDangKyBienDong").Rows(j).Item(0).ToString

                        .Range(.Cells(iDongHienThoi + 1, "B"), .Cells(iDongHienThoi + 3, "C")).MergeCells = True
                        .Range(.Cells(iDongHienThoi + 1, "B"), .Cells(iDongHienThoi + 3, "C")).WrapText = True
                        .Range(.Cells(iDongHienThoi + 1, "B"), .Cells(iDongHienThoi + 3, "C")).VerticalAlignment = -4160
                        .Range(.Cells(iDongHienThoi + 1, "B"), .Cells(iDongHienThoi + 3, "C")).Value = dss.Tables("NguoiDangKyBienDong").Rows(j).Item(1).ToString

                        For k = -3 To 3
                            .Range(.Cells(iDongHienThoi + k, "D"), .Cells(iDongHienThoi + k, "E")).MergeCells = True
                        Next

                        .Range(.Cells(iDongHienThoi, "D"), .Cells(iDongHienThoi, "E")).Value = dss.Tables("NguoiDangKyBienDong").Rows(j).Item(2).Date()
                        .Range(.Cells(iDongHienThoi + 1, "D"), .Cells(iDongHienThoi + 1, "E")).Value = dss.Tables("NguoiDangKyBienDong").Rows(j).Item(2).Hour() & "g" & dss.Tables("NguoiDangKyBienDong").Rows(j).Item(2).Minute() & "'"
                        '---------------------------------
                        If dss.Tables("NguoiDangKyBienDong").Rows(j).Item("ToBanDo") Is DBNull.Value Then
                            sTam = ""
                        Else
                            sTam = CStr(dss.Tables("NguoiDangKyBienDong").Rows(j).Item("ToBanDo"))
                        End If


                        .Range(.Cells(iDongHienThoi, "F"), .Cells(iDongHienThoi, "F")).Value = sTam
                        '-------------------------------
                        If dss.Tables("NguoiDangKyBienDong").Rows(j).Item("SoThua") Is DBNull.Value Then
                            sTam = ""
                        Else
                            sTam = CStr(dss.Tables("NguoiDangKyBienDong").Rows(j).Item("SoThua"))
                        End If


                        .Range(.Cells(iDongHienThoi, "G"), .Cells(iDongHienThoi, "G")).Value = sTam
                        '--------------------------------
                        .Range(.Cells(iDongHienThoi, "H"), .Cells(iDongHienThoi + 3, "P")).MergeCells = True
                        .Range(.Cells(iDongHienThoi, "H"), .Cells(iDongHienThoi + 3, "P")).WrapText = True
                        .Range(.Cells(iDongHienThoi, "H"), .Cells(iDongHienThoi + 3, "P")).VerticalAlignment = -4160
                        .Range(.Cells(iDongHienThoi, "H"), .Cells(iDongHienThoi + 3, "P")).Value = dss.Tables("NguoiDangKyBienDong").Rows(j).Item(5)
                        .Range(.Cells(iDongHienThoi, "H"), .Cells(iDongHienThoi + 3, "P")).NumberFormat = "00000"

                        iDongHienThoi += 4
                    Next
                    iDau = iDongHienThoi
                Else
                    If NamIn = "0" Then


                        iDautruoc = iDau

                        If iDau + 6 > iTrang * 68 + 71 Then
                            iDau = (iTrang * 68 + 71) + 2 ' chuyen diem du den dau trang toi
                            iTrang += 1
                        End If

                        .Range(.Cells(iDau - 1, "A"), .Cells(iDautruoc - (4 * j + 3) - iKhoangtang, "P")).BorderAround(, 2, -4105)

                        iKhoangtang = 0

                        .Range(.Cells(iDau, "B"), .Cells(iDau + 1, "C")).MergeCells = True
                        .Range(.Cells(iDau, "B"), .Cells(iDau + 1, "C")).BorderAround(, 2, -4105)
                        .Range(.Cells(iDau, "B"), .Cells(iDau + 1, "C")).Font.Size = 16
                        .Range(.Cells(iDau, "B"), .Cells(iDau + 1, "C")).WrapText = True
                        .Range(.Cells(iDau, "B"), .Cells(iDau + 1, "C")).Font.Bold = True
                        .Range(.Cells(iDau, "B"), .Cells(iDau + 1, "C")).HorizontalAlignment = -4108
                        .Range(.Cells(iDau, "B"), .Cells(iDau + 1, "C")).VerticalAlignment = -4108
                        .Range(.Cells(iDau, "B"), .Cells(iDau + 1, "C")).Value = "Năm " & nam(i)
                        Dim Nam1 As String = ""
                        Nam1 = nam(i)
                        iDongHienThoi = iDau + 3
                        cmd.Connection = conn
                        cmd.CommandType = CommandType.Text

                        cmd.CommandText = "SELECT DISTINCT tblDangKyBienDong.TenNguoiDangKy, tblDangKyBienDong.DiaChiNguoiDangKy,tblDangKyBienDong.ThoiDiemDangKy, tblThuaDatCapGCN.ToBanDo, tblThuaDatCapGCN.SoThua, tblDangKyBienDong.NoidungSoBienDong,tblTuDienDonViHanhChinh.MaDonViHanhChinh" & _
                                        " FROM tblDangKyBienDong INNER JOIN tblHoSoCapGCN ON tblHoSoCapGCN.MaHoSoCapGCN = tblDangKyBienDong.MaHoSoCapGCN " & _
                                       " INNER JOIN tblThuaDatCapGCN ON tblDangKyBienDong.MaHoSoCapGCN = tblThuaDatCapGCN.MaHoSoCapGCN " & _
                                       " INNER JOIN tblTuDienDonViHanhChinh ON tblTuDienDonViHanhChinh.MaXa=tblHoSoCapGCN.MaXa       " & _
                                       " WHERE tblTuDienDonViHanhChinh.MaDonViHanhChinh='" + MaDonViHanhChinh + "' AND year(tblDangKyBienDong.ThoiDiemDangKy)='" + Nam1 + "'  ORDER BY tblDangKyBienDong.ThoiDiemDangKy"
                        da.SelectCommand = cmd
                        dss.Tables.Clear()
                        da.Fill(dss, "NguoiDangKyBienDong")


                        For k As Integer = 0 To dss.Tables("NguoiDangKyBienDong").Rows.Count * 4
                            .Range(.Cells(iDongHienThoi + k - 3, "H"), .Cells(iDongHienThoi + k - 3, "P")).MergeCells = True
                        Next

                        For j = 0 To dss.Tables("NguoiDangKyBienDong").Rows.Count - 1

                            If iDongHienThoi + 3 > iTrang * 68 + 71 Then
                                iKhoangtang = (iTrang * 68 + 71) - iDongHienThoi + 2
                                iDongHienThoi += iKhoangtang
                                iTrang += 1
                            End If

                            sTam = ""
                            If j + 1 < 10 Then
                                sTam = "0"
                            End If
                            sTam += CStr(j + 1)

                            .Range(.Cells(iDongHienThoi, "A"), .Cells(iDongHienThoi, "A")).Value = sTam

                            .Range(.Cells(iDongHienThoi - 1, "B"), .Cells(iDongHienThoi - 1, "C")).MergeCells = True
                            .Range(.Cells(iDongHienThoi, "B"), .Cells(iDongHienThoi, "C")).MergeCells = True
                            .Range(.Cells(iDongHienThoi, "B"), .Cells(iDongHienThoi, "C")).Value = dss.Tables("NguoiDangKyBienDong").Rows(j).Item(0).ToString

                            .Range(.Cells(iDongHienThoi + 1, "B"), .Cells(iDongHienThoi + 3, "C")).MergeCells = True
                            .Range(.Cells(iDongHienThoi + 1, "B"), .Cells(iDongHienThoi + 3, "C")).WrapText = True
                            .Range(.Cells(iDongHienThoi + 1, "B"), .Cells(iDongHienThoi + 3, "C")).VerticalAlignment = -4160
                            .Range(.Cells(iDongHienThoi + 1, "B"), .Cells(iDongHienThoi + 3, "C")).Value = dss.Tables("NguoiDangKyBienDong").Rows(j).Item(1).ToString

                            For k = -3 To 3
                                .Range(.Cells(iDongHienThoi + k, "D"), .Cells(iDongHienThoi + k, "E")).MergeCells = True
                            Next

                            .Range(.Cells(iDongHienThoi, "D"), .Cells(iDongHienThoi, "E")).Value = dss.Tables("NguoiDangKyBienDong").Rows(j).Item(2).Date()
                            .Range(.Cells(iDongHienThoi + 1, "D"), .Cells(iDongHienThoi + 1, "E")).Value = dss.Tables("NguoiDangKyBienDong").Rows(j).Item(2).Hour() & "g" & dss.Tables("NguoiDangKyBienDong").Rows(j).Item(2).Minute() & "'"
                            '---------------------------------
                            If dss.Tables("NguoiDangKyBienDong").Rows(j).Item("ToBanDo") Is DBNull.Value Then
                                sTam = ""
                            Else
                                sTam = CStr(dss.Tables("NguoiDangKyBienDong").Rows(j).Item("ToBanDo"))
                            End If


                            .Range(.Cells(iDongHienThoi, "F"), .Cells(iDongHienThoi, "F")).Value = sTam
                            '-------------------------------
                            If dss.Tables("NguoiDangKyBienDong").Rows(j).Item("SoThua") Is DBNull.Value Then
                                sTam = ""
                            Else
                                sTam = CStr(dss.Tables("NguoiDangKyBienDong").Rows(j).Item("SoThua"))
                            End If


                            .Range(.Cells(iDongHienThoi, "G"), .Cells(iDongHienThoi, "G")).Value = sTam
                            '--------------------------------
                            .Range(.Cells(iDongHienThoi, "H"), .Cells(iDongHienThoi + 3, "P")).MergeCells = True
                            .Range(.Cells(iDongHienThoi, "H"), .Cells(iDongHienThoi + 3, "P")).WrapText = True

                            .Range(.Cells(iDongHienThoi, "H"), .Cells(iDongHienThoi + 3, "P")).VerticalAlignment = -4160
                            .Range(.Cells(iDongHienThoi, "H"), .Cells(iDongHienThoi + 3, "P")).Value = dss.Tables("NguoiDangKyBienDong").Rows(j).Item(5)
                            .Range(.Cells(iDongHienThoi, "H"), .Cells(iDongHienThoi + 3, "P")).NumberFormat = "00000"

                            iDongHienThoi += 4
                        Next
                        iDau = iDongHienThoi
                    End If
                End If
            Next

        End With
    End Sub
#End Region
#Region "SoDiaChinh"
    Public Sub TrangMoDauSoDiaChinh(ByVal app As Object, ByVal MaDonViHanhChinh() As String)

        Dim tinh As String = MaDonViHanhChinh(0)
        Dim huyen As String = MaDonViHanhChinh(1)
        Dim xa As String = MaDonViHanhChinh(2)
        Dim matinh01 As Integer = 0
        Dim matinh02 As Integer = 1

        Dim mahuyen01 As Integer = 0
        Dim mahuyen02 As Integer = 0
        Dim mahuyen03 As Integer = 1

        Dim maxa01 As Integer = 0
        Dim maxa02 As Integer = 0
        Dim maxa03 As Integer = 0
        Dim maxa04 As Integer = 2
        Dim maxa05 As Integer = 8
        Dim soquyen01 As String = 0
        Dim soquyen02 As String = 1
        Dim soquyen03 As String = 0

        Dim ngay As String = CStr(Now.Day)
        If Len(ngay) = 1 Then ngay = "0" + ngay

        Dim thang As String = CStr(Now.Month)
        If Len(thang) = 1 Then thang = "0" + thang

        With app
            .Cells.RowHeight = 15.75
            .Cells.Font.Size = 12

            For j As Integer = 1 To 25
                .Cells(23, j).ColumnWidth = 4.2
            Next

            .Cells(23, "D").ColumnWidth = 5.29
            .Cells(23, "E").ColumnWidth = 5.29
            .Cells(23, "F").ColumnWidth = 4.43


            .Range(.Cells(1, "A"), .Cells(7, "Y")).VerticalAlignment = -4160      '= -4107
            .Range(.Cells(32, "Q"), .Cells(43, "U")).VerticalAlignment = -4108      '= -4108
            .Range(.Cells(32, "E"), .Cells(47, "O")).VerticalAlignment = -4160      '= -4160

            .Range(.Cells(1, "A"), .Cells(28, "Y")).HorizontalAlignment = -4108 '= -4108
            .Range(.Cells(32, "Q"), .Cells(43, "U")).HorizontalAlignment = -4108 '= -4108
            .Range(.Cells(51, "C"), .Cells(54, "V")).HorizontalAlignment = -4108 '= -4108
            .Range(.Cells(33, "E"), .Cells(37, "P")).HorizontalAlignment = -4131      '= Constants.xlLeft
            .Range(.Cells(43, "M"), .Cells(43, "M")).HorizontalAlignment = -4152      '= Constants.xlRight

            .Range(.Cells(1, "I"), .Cells(1, "V")).Font.Bold = True
            .Range(.Cells(18, "G"), .Cells(18, "G")).Font.Bold = True
            .Range(.Cells(32, "E"), .Cells(44, "O")).Font.Bold = True
            .Range(.Cells(32, "E"), .Cells(44, "O")).Font.Size = 14
            .Range(.Cells(1, "I"), .Cells(1, "V")).Font.Size = 14
            'Ban hành kèm theo thông tư.......
            .Range(.Cells(1, "A"), .Cells(6, "H")).MergeCells = True
            .Range(.Cells(1, "A"), .Cells(6, "H")).Value = "Ban hành kèm theo thông tư                                                  Số 09/2007/TT-BTNMT                                                                              Ngày 02/08/2007 của Bộ Tài nguyên                                                           và Môi trường hướng dẫn lập, chỉnh lý,                                            quản lý hồ sơ địa chính"
            .Range(.Cells(1, "A"), .Cells(6, "H")).WrapText = True
            .Range(.Cells(1, "A"), .Cells(1, "A")).Font.Size = 13
            ' Cộng hòa xã hội chủ nghĩa việt nam...
            .Range(.Cells(1, "I"), .Cells(3, "U")).MergeCells = True
            .Range(.Cells(1, "I"), .Cells(3, "U")).Value = "CỘNG HÒA XÃ HỘI CHỦ NGHĨA VIỆT NAM                              Độc Lập - Tự Do - Hạnh Phúc"
            .Range(.Cells(1, "I"), .Cells(3, "U")).WrapText = True
            ' Mẫu số..
            .Range(.Cells(1, "V"), .Cells(2, "Y")).MergeCells = True
            .Range(.Cells(1, "V"), .Cells(2, "Y")).Value = "Mẫu số: 01/ĐK"
            'Tiêu đề
            .Range(.Cells(18, "G"), .Cells(28, "T")).MergeCells = True
            .Range(.Cells(18, "G"), .Cells(28, "T")).Font.Size = 60
            .Range(.Cells(18, "G"), .Cells(28, "T")).Value = "SỔ                                                                   ĐỊA CHÍNH"
            .Range(.Cells(18, "G"), .Cells(28, "T")).WrapText = True
            '========================================
            .Range(.Cells(32, "A"), .Cells(32, "A")).RowHeight = 18.75
            .Range(.Cells(32, "E"), .Cells(33, "F")).MergeCells = True
            .Range(.Cells(32, "E"), .Cells(33, "F")).Value = "TỈNH:"
            .Range(.Cells(32, "G"), .Cells(33, "N")).MergeCells = True
            .Range(.Cells(32, "G"), .Cells(33, "N")).Value = tinh
            '======
            .Range(.Cells(32, "P"), .Cells(33, "O")).MergeCells = True
            .Range(.Cells(32, "P"), .Cells(33, "O")).Value = "Mã:"
            .Range(.Cells(32, "Q"), .Cells(32, "Q")).Value = matinh01
            .Range(.Cells(32, "R"), .Cells(32, "R")).Value = matinh02
            '===========
            .Range(.Cells(32, "Q"), .Cells(32, "Q")).BorderAround(, 2, -4105) 'XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic
            .Range(.Cells(32, "R"), .Cells(32, "R")).BorderAround(, 2, -4105)
            '===================================
            .Range(.Cells(34, "A"), .Cells(34, "A")).RowHeight = 18.75
            .Range(.Cells(34, "E"), .Cells(35, "F")).MergeCells = True
            .Range(.Cells(34, "E"), .Cells(35, "F")).Value = "HUYỆN:"
            .Range(.Cells(34, "G"), .Cells(35, "N")).MergeCells = True
            .Range(.Cells(34, "G"), .Cells(35, "N")).Value = huyen
            '=======
            .Range(.Cells(34, "P"), .Cells(35, "O")).MergeCells = True
            .Range(.Cells(34, "P"), .Cells(35, "O")).Value = "Mã:"
            .Range(.Cells(34, "Q"), .Cells(34, "Q")).Value = mahuyen01
            .Range(.Cells(34, "R"), .Cells(34, "R")).Value = mahuyen02
            .Range(.Cells(34, "S"), .Cells(34, "S")).Value = mahuyen03
            '=============
            .Range(.Cells(34, "Q"), .Cells(34, "Q")).BorderAround(, 2, -4105)
            .Range(.Cells(34, "R"), .Cells(34, "R")).BorderAround(, 2, -4105)
            .Range(.Cells(34, "S"), .Cells(34, "S")).BorderAround(, 2, -4105)
            '===================================
            .Range(.Cells(36, "A"), .Cells(36, "A")).RowHeight = 18.75
            .Range(.Cells(36, "E"), .Cells(37, "F")).MergeCells = True
            .Range(.Cells(36, "E"), .Cells(37, "F")).Value = "XÃ:"
            .Range(.Cells(36, "G"), .Cells(37, "N")).MergeCells = True
            .Range(.Cells(36, "G"), .Cells(37, "N")).Value = xa
            '==============
            .Range(.Cells(36, "P"), .Cells(37, "O")).MergeCells = True
            .Range(.Cells(36, "P"), .Cells(37, "O")).Value = "Mã:"
            .Range(.Cells(36, "Q"), .Cells(36, "Q")).Value = maxa01
            .Range(.Cells(36, "R"), .Cells(36, "R")).Value = maxa02
            .Range(.Cells(36, "S"), .Cells(36, "S")).Value = maxa03
            .Range(.Cells(36, "T"), .Cells(36, "T")).Value = maxa04
            .Range(.Cells(36, "U"), .Cells(36, "U")).Value = maxa05
            '============
            .Range(.Cells(36, "Q"), .Cells(36, "Q")).BorderAround(, 2, -4105)
            .Range(.Cells(36, "R"), .Cells(36, "R")).BorderAround(, 2, -4105)
            .Range(.Cells(36, "S"), .Cells(36, "S")).BorderAround(, 2, -4105)
            .Range(.Cells(36, "T"), .Cells(36, "T")).BorderAround(, 2, -4105)
            .Range(.Cells(36, "U"), .Cells(36, "U")).BorderAround(, 2, -4105)
            '===============================================
            .Range(.Cells(43, "A"), .Cells(43, "A")).RowHeight = 18.75
            .Range(.Cells(43, "M"), .Cells(44, "O")).MergeCells = True
            .Range(.Cells(43, "M"), .Cells(43, "M")).Value = "Quyển số:"
            '===============
            .Range(.Cells(43, "Q"), .Cells(43, "Q")).Value = soquyen01
            .Range(.Cells(43, "R"), .Cells(43, "R")).Value = soquyen02
            .Range(.Cells(43, "S"), .Cells(43, "S")).Value = soquyen03

            .Range(.Cells(43, "Q"), .Cells(43, "Q")).BorderAround(, 2, -4105)
            .Range(.Cells(43, "R"), .Cells(43, "R")).BorderAround(, 2, -4105)
            .Range(.Cells(43, "S"), .Cells(43, "S")).BorderAround(, 2, -4105)
            '==========
            .Range(.Cells(51, "C"), .Cells(51, "L")).MergeCells = True
            .Range(.Cells(51, "C"), .Cells(51, "L")).Value = tinh & ", Ngày " & ngay & " tháng " & thang & " năm " & Now.Year '"......., Ngày ... tháng ... năm ..."

            .Range(.Cells(52, "C"), .Cells(52, "L")).MergeCells = True
            .Range(.Cells(52, "C"), .Cells(52, "L")).Value = "GIÁM ĐỐC"

            .Range(.Cells(53, "C"), .Cells(53, "L")).MergeCells = True
            .Range(.Cells(53, "C"), .Cells(53, "L")).Value = "VĂN PHÒNG ĐĂNG KÝ QUYỀN SỬ DỤNG ĐẤT"

            .Range(.Cells(54, "C"), .Cells(54, "L")).MergeCells = True
            .Range(.Cells(54, "C"), .Cells(54, "L")).Value = "Ký, Đóng dấu"

            .Range(.Cells(51, "O"), .Cells(51, "V")).MergeCells = True
            .Range(.Cells(51, "O"), .Cells(51, "V")).Value = tinh & ", Ngày " & ngay & " tháng " & thang & " năm " & Now.Year

            .Range(.Cells(52, "O"), .Cells(52, "V")).MergeCells = True
            .Range(.Cells(52, "O"), .Cells(52, "V")).Value = "GIÁM ĐỐC"

            .Range(.Cells(53, "O"), .Cells(53, "V")).MergeCells = True
            .Range(.Cells(53, "O"), .Cells(53, "V")).Value = "SỞ TÀI NGUYÊN VÀ MÔI TRƯỜNG"

            .Range(.Cells(54, "O"), .Cells(54, "V")).MergeCells = True
            .Range(.Cells(54, "O"), .Cells(54, "V")).Value = "Ký, Đóng dấu"

        End With
    End Sub
    Public Sub KhungSoDiaChinh(ByVal app As Object, ByVal iTrang As Integer, ByVal iSoDongCuaTrang As Integer)

        With app
            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "J"), .Cells(68 + iSoDongCuaTrang * iTrang, "P")).MergeCells = True
            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "A"), .Cells(106 + iSoDongCuaTrang * iTrang, "Y")).BorderAround(, 2, -4105)
            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "J"), .Cells(68 + iSoDongCuaTrang * iTrang, "P")).Value = "I-NGƯỜI SỬ DỤNG ĐẤT"
            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "J"), .Cells(68 + iSoDongCuaTrang * iTrang, "P")).HorizontalAlignment = -4108
            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "J"), .Cells(68 + iSoDongCuaTrang * iTrang, "P")).VerticalAlignment = -4108 ' center
            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "J"), .Cells(68 + iSoDongCuaTrang * iTrang, "P")).Font.Size = 13
            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "J"), .Cells(68 + iSoDongCuaTrang * iTrang, "P")).Font.Bold = True
            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "J"), .Cells(68 + iSoDongCuaTrang * iTrang, "P")).RowHeight = 20.25
            '==================
            .Range(.Cells(69 + iSoDongCuaTrang * iTrang, "A"), .Cells(74 + iSoDongCuaTrang * iTrang, "Y")).MergeCells = True
            .Range(.Cells(69 + iSoDongCuaTrang * iTrang, "A"), .Cells(74 + iSoDongCuaTrang * iTrang, "Y")).BorderAround(, 2, -4105)
            .Range(.Cells(69 + iSoDongCuaTrang * iTrang, "A"), .Cells(74 + iSoDongCuaTrang * iTrang, "Y")).WrapText = True
            .Range(.Cells(69 + iSoDongCuaTrang * iTrang, "A"), .Cells(74 + iSoDongCuaTrang * iTrang, "Y")).HorizontalAlignment = -4131 'left
            .Range(.Cells(69 + iSoDongCuaTrang * iTrang, "A"), .Cells(74 + iSoDongCuaTrang * iTrang, "Y")).VerticalAlignment = -4160 ' top
            .Range(.Cells(69 + iSoDongCuaTrang * iTrang, "A"), .Cells(74 + iSoDongCuaTrang * iTrang, "Y")).Font.Italic = True
            '=====================
            .Range(.Cells(75 + iSoDongCuaTrang * iTrang, "K"), .Cells(75 + iSoDongCuaTrang * iTrang, "N")).MergeCells = True
            .Range(.Cells(75 + iSoDongCuaTrang * iTrang, "K"), .Cells(75 + iSoDongCuaTrang * iTrang, "N")).Value = "II-THỬA ĐẤT"
            .Range(.Cells(75 + iSoDongCuaTrang * iTrang, "K"), .Cells(75 + iSoDongCuaTrang * iTrang, "N")).Font.Size = 13
            .Range(.Cells(75 + iSoDongCuaTrang * iTrang, "K"), .Cells(75 + iSoDongCuaTrang * iTrang, "N")).Font.Bold = True
            .Range(.Cells(75 + iSoDongCuaTrang * iTrang, "K"), .Cells(75 + iSoDongCuaTrang * iTrang, "N")).RowHeight = 20.25
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "K"), .Cells(76 + iSoDongCuaTrang * iTrang, "N")).RowHeight = 20.25
            .Range(.Cells(77 + iSoDongCuaTrang * iTrang, "K"), .Cells(77 + iSoDongCuaTrang * iTrang, "N")).RowHeight = 20.25
            .Range(.Cells(107 + iSoDongCuaTrang * iTrang, "K"), .Cells(107 + iSoDongCuaTrang * iTrang, "N")).RowHeight = 20.25

            'For hang As Integer = 0 To 20
            '    .Range(.Cells(78 + hang + iSoDongCuaTrang * iTrang, "A"), .Cells(78 + hang + iSoDongCuaTrang * iTrang, "C")).MergeCells = True
            '    .Range(.Cells(78 + hang + iSoDongCuaTrang * iTrang, "D"), .Cells(78 + hang + iSoDongCuaTrang * iTrang, "E")).MergeCells = True
            '    .Range(.Cells(78 + hang + iSoDongCuaTrang * iTrang, "F"), .Cells(78 + hang + iSoDongCuaTrang * iTrang, "G")).MergeCells = True
            '    .Range(.Cells(78 + hang + iSoDongCuaTrang * iTrang, "H"), .Cells(78 + hang + iSoDongCuaTrang * iTrang, "I")).MergeCells = True
            '    .Range(.Cells(78 + hang + iSoDongCuaTrang * iTrang, "J"), .Cells(78 + hang + iSoDongCuaTrang * iTrang, "K")).MergeCells = True
            '    .Range(.Cells(78 + hang + iSoDongCuaTrang * iTrang, "L"), .Cells(78 + hang + iSoDongCuaTrang * iTrang, "M")).MergeCells = True
            '    .Range(.Cells(78 + hang + iSoDongCuaTrang * iTrang, "N"), .Cells(78 + hang + iSoDongCuaTrang * iTrang, "p")).MergeCells = True
            '    .Range(.Cells(78 + hang + iSoDongCuaTrang * iTrang, "Q"), .Cells(78 + hang + iSoDongCuaTrang * iTrang, "S")).MergeCells = True
            '    .Range(.Cells(78 + hang + iSoDongCuaTrang * iTrang, "T"), .Cells(78 + hang + iSoDongCuaTrang * iTrang, "V")).MergeCells = True
            '    .Range(.Cells(78 + hang + iSoDongCuaTrang * iTrang, "W"), .Cells(78 + hang + iSoDongCuaTrang * iTrang, "Y")).MergeCells = True
            'Next

            'For hang = 0 To 31
            '    .Range(.Cells(109 + hang + iSoDongCuaTrang * iTrang, "A"), .Cells(109 + hang + iSoDongCuaTrang * iTrang, "C")).MergeCells = True
            '    .Range(.Cells(109 + hang + iSoDongCuaTrang * iTrang, "D"), .Cells(109 + hang + iSoDongCuaTrang * iTrang, "F")).MergeCells = True
            '    .Range(.Cells(109 + hang + iSoDongCuaTrang * iTrang, "G"), .Cells(109 + hang + iSoDongCuaTrang * iTrang, "Y")).MergeCells = True
            'Next

            '==== ke cac cot
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "A"), .Cells(106 + iSoDongCuaTrang * iTrang, "Y")).BorderAround(, 2, -4105)
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "D"), .Cells(106 + iSoDongCuaTrang * iTrang, "E")).BorderAround(, 2, -4105)
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "H"), .Cells(106 + iSoDongCuaTrang * iTrang, "K")).BorderAround(, 2, -4105)
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "N"), .Cells(106 + iSoDongCuaTrang * iTrang, "P")).BorderAround(, 2, -4105)
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "T"), .Cells(106 + iSoDongCuaTrang * iTrang, "V")).BorderAround(, 2, -4105)

            .Range(.Cells(77 + iSoDongCuaTrang * iTrang, "I"), .Cells(106 + iSoDongCuaTrang * iTrang, "I")).Borders(&HA).Weight = &H2 '&H2 = XlBorderWeight.xlThin ' &HA = XlBordersIndex.xlEdgeRight
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "H"), .Cells(74 + iSoDongCuaTrang * iTrang, "K")).Borders(&H9).Weight = &H2         '&H9 = XlBordersIndex.xlEdgeBottom
            .Range(.Cells(110 + iSoDongCuaTrang * iTrang, "A"), .Cells(110 + iSoDongCuaTrang * iTrang, "Y")).Borders(&H9).Weight = &H2
            '================================================
            .Range(.Cells(107 + iSoDongCuaTrang * iTrang, "A"), .Cells(107 + iSoDongCuaTrang * iTrang, "Y")).BorderAround(, 2, -4105)
            .Range(.Cells(107 + iSoDongCuaTrang * iTrang, "E"), .Cells(107 + iSoDongCuaTrang * iTrang, "U")).MergeCells = True
            .Range(.Cells(107 + iSoDongCuaTrang * iTrang, "E"), .Cells(107 + iSoDongCuaTrang * iTrang, "U")).Value = "III-NHỮNG THAY ĐỔI TRONG QUÁ TRÌNH SỬ DỤNG ĐẤT VÀ GHI CHÚ"
            .Range(.Cells(107 + iSoDongCuaTrang * iTrang, "E"), .Cells(107 + iSoDongCuaTrang * iTrang, "U")).Font.Size = 13
            .Range(.Cells(107 + iSoDongCuaTrang * iTrang, "E"), .Cells(107 + iSoDongCuaTrang * iTrang, "U")).Font.Bold = True
            .Range(.Cells(108 + iSoDongCuaTrang * iTrang, "E"), .Cells(107 + iSoDongCuaTrang * iTrang, "U")).Font.Bold = True
            ''===================================
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "A"), .Cells(78 + iSoDongCuaTrang * iTrang, "C")).MergeCells = True
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "D"), .Cells(78 + iSoDongCuaTrang * iTrang, "E")).MergeCells = True
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "F"), .Cells(78 + iSoDongCuaTrang * iTrang, "G")).MergeCells = True
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "H"), .Cells(76 + iSoDongCuaTrang * iTrang, "K")).MergeCells = True
            .Range(.Cells(77 + iSoDongCuaTrang * iTrang, "H"), .Cells(78 + iSoDongCuaTrang * iTrang, "I")).MergeCells = True
            .Range(.Cells(77 + iSoDongCuaTrang * iTrang, "J"), .Cells(78 + iSoDongCuaTrang * iTrang, "K")).MergeCells = True
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "L"), .Cells(78 + iSoDongCuaTrang * iTrang, "M")).MergeCells = True
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "N"), .Cells(78 + iSoDongCuaTrang * iTrang, "P")).MergeCells = True
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "Q"), .Cells(78 + iSoDongCuaTrang * iTrang, "S")).MergeCells = True
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "T"), .Cells(78 + iSoDongCuaTrang * iTrang, "V")).MergeCells = True
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "W"), .Cells(78 + iSoDongCuaTrang * iTrang, "Y")).MergeCells = True
            ' ''==========
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "A"), .Cells(78 + iSoDongCuaTrang * iTrang, "Y")).WrapText = True
            .Range(.Cells(108 + iSoDongCuaTrang * iTrang, "A"), .Cells(110 + iSoDongCuaTrang * iTrang, "Y")).WrapText = True
            ' ''=====================
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "A"), .Cells(78 + iSoDongCuaTrang * iTrang, "C")).Value = "Ngày tháng năm vào sổ"
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "D"), .Cells(78 + iSoDongCuaTrang * iTrang, "E")).Value = "Số thứ tự thửa đất"
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "F"), .Cells(78 + iSoDongCuaTrang * iTrang, "G")).Value = "Số thứ tự tờ bản đồ"
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "H"), .Cells(76 + iSoDongCuaTrang * iTrang, "K")).Value = "Diện tích sử dụng (m2)"
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "H"), .Cells(76 + iSoDongCuaTrang * iTrang, "K")).Font.Size = 10
            .Range(.Cells(77 + iSoDongCuaTrang * iTrang, "H"), .Cells(78 + iSoDongCuaTrang * iTrang, "I")).Value = "Riêng"
            .Range(.Cells(77 + iSoDongCuaTrang * iTrang, "J"), .Cells(78 + iSoDongCuaTrang * iTrang, "K")).Value = "Chung"
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "L"), .Cells(78 + iSoDongCuaTrang * iTrang, "M")).Value = "Mục đích sử dụng"
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "N"), .Cells(78 + iSoDongCuaTrang * iTrang, "P")).Value = "Thời hạn sử dụng"
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "Q"), .Cells(78 + iSoDongCuaTrang * iTrang, "S")).Value = "Nguồn gốc sử dụng"
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "T"), .Cells(78 + iSoDongCuaTrang * iTrang, "V")).Value = "Số phát hành GCN QSDĐ"
            .Range(.Cells(76 + iSoDongCuaTrang * iTrang, "W"), .Cells(78 + iSoDongCuaTrang * iTrang, "Y")).Value = "Số vào sổ cấp GCN QSDĐ"
            ' ''===========================================
            .Range(.Cells(79 + iSoDongCuaTrang * iTrang, "A"), .Cells(79 + iSoDongCuaTrang * iTrang, "Y")).BorderAround(, 2, -4105)
            .Range(.Cells(79 + iSoDongCuaTrang * iTrang, "A"), .Cells(79 + iSoDongCuaTrang * iTrang, "C")).MergeCells = True
            .Range(.Cells(79 + iSoDongCuaTrang * iTrang, "D"), .Cells(79 + iSoDongCuaTrang * iTrang, "E")).MergeCells = True
            .Range(.Cells(79 + iSoDongCuaTrang * iTrang, "F"), .Cells(79 + iSoDongCuaTrang * iTrang, "G")).MergeCells = True
            .Range(.Cells(79 + iSoDongCuaTrang * iTrang, "H"), .Cells(79 + iSoDongCuaTrang * iTrang, "I")).MergeCells = True
            .Range(.Cells(79 + iSoDongCuaTrang * iTrang, "J"), .Cells(79 + iSoDongCuaTrang * iTrang, "K")).MergeCells = True
            .Range(.Cells(79 + iSoDongCuaTrang * iTrang, "L"), .Cells(79 + iSoDongCuaTrang * iTrang, "M")).MergeCells = True
            .Range(.Cells(79 + iSoDongCuaTrang * iTrang, "N"), .Cells(79 + iSoDongCuaTrang * iTrang, "P")).MergeCells = True
            .Range(.Cells(79 + iSoDongCuaTrang * iTrang, "Q"), .Cells(79 + iSoDongCuaTrang * iTrang, "S")).MergeCells = True
            .Range(.Cells(79 + iSoDongCuaTrang * iTrang, "T"), .Cells(79 + iSoDongCuaTrang * iTrang, "V")).MergeCells = True
            .Range(.Cells(79 + iSoDongCuaTrang * iTrang, "W"), .Cells(79 + iSoDongCuaTrang * iTrang, "Y")).MergeCells = True
            '===========
            .Range(.Cells(79 + iSoDongCuaTrang * iTrang, "A"), .Cells(79 + iSoDongCuaTrang * iTrang, "Y")).NumberFormat = "(0)"
            .Range(.Cells(79 + iSoDongCuaTrang * iTrang, "A"), .Cells(79 + iSoDongCuaTrang * iTrang, "Y")).Font.Size = 10
            .Range(.Cells(79 + iSoDongCuaTrang * iTrang, "A"), .Cells(79 + iSoDongCuaTrang * iTrang, "C")).Value = "1"
            .Range(.Cells(79 + iSoDongCuaTrang * iTrang, "D"), .Cells(79 + iSoDongCuaTrang * iTrang, "E")).Value = "2"
            .Range(.Cells(79 + iSoDongCuaTrang * iTrang, "F"), .Cells(79 + iSoDongCuaTrang * iTrang, "G")).Value = "3"
            .Range(.Cells(79 + iSoDongCuaTrang * iTrang, "H"), .Cells(79 + iSoDongCuaTrang * iTrang, "I")).Value = "4"
            .Range(.Cells(79 + iSoDongCuaTrang * iTrang, "J"), .Cells(79 + iSoDongCuaTrang * iTrang, "K")).Value = "5"
            .Range(.Cells(79 + iSoDongCuaTrang * iTrang, "L"), .Cells(79 + iSoDongCuaTrang * iTrang, "M")).Value = "6"
            .Range(.Cells(79 + iSoDongCuaTrang * iTrang, "N"), .Cells(79 + iSoDongCuaTrang * iTrang, "P")).Value = "7"
            .Range(.Cells(79 + iSoDongCuaTrang * iTrang, "Q"), .Cells(79 + iSoDongCuaTrang * iTrang, "S")).Value = "8"
            .Range(.Cells(79 + iSoDongCuaTrang * iTrang, "T"), .Cells(79 + iSoDongCuaTrang * iTrang, "V")).Value = "9"
            .Range(.Cells(79 + iSoDongCuaTrang * iTrang, "W"), .Cells(79 + iSoDongCuaTrang * iTrang, "Y")).Value = "10"
            ''=======
            .Range(.Cells(108 + iSoDongCuaTrang * iTrang, "A"), .Cells(110 + iSoDongCuaTrang * iTrang, "C")).MergeCells = True
            .Range(.Cells(108 + iSoDongCuaTrang * iTrang, "A"), .Cells(134 + iSoDongCuaTrang * iTrang, "C")).BorderAround(, 2, -4105)
            .Range(.Cells(108 + iSoDongCuaTrang * iTrang, "A"), .Cells(110 + iSoDongCuaTrang * iTrang, "C")).Value = "Số thứ tự         thửa đất "

            .Range(.Cells(108 + iSoDongCuaTrang * iTrang, "D"), .Cells(110 + iSoDongCuaTrang * iTrang, "F")).MergeCells = True
            .Range(.Cells(108 + iSoDongCuaTrang * iTrang, "D"), .Cells(134 + iSoDongCuaTrang * iTrang, "F")).BorderAround(, 2, -4105)
            .Range(.Cells(108 + iSoDongCuaTrang * iTrang, "D"), .Cells(110 + iSoDongCuaTrang * iTrang, "F")).Value = "Ngày tháng          năm"

            .Range(.Cells(108 + iSoDongCuaTrang * iTrang, "G"), .Cells(110 + iSoDongCuaTrang * iTrang, "Y")).MergeCells = True
            .Range(.Cells(108 + iSoDongCuaTrang * iTrang, "G"), .Cells(134 + iSoDongCuaTrang * iTrang, "Y")).BorderAround(, 2, -4105)
            .Range(.Cells(108 + iSoDongCuaTrang * iTrang, "G"), .Cells(110 + iSoDongCuaTrang * iTrang, "Y")).Value = "Nội dung ghi chú hoặc biến động và căn cứ pháp lý"
            '=================================
            '.Range(.Cells(80 + iSoDongCuaTrang * iTrang, "A"), .Cells(136 + iSoDongCuaTrang * iTrang, "C")).NumberFormat = "dd-mm-yyyy"
            '.Range(.Cells(80 + iSoDongCuaTrang * iTrang, "N"), .Cells(136 + iSoDongCuaTrang * iTrang, "P")).NumberFormat = "mm-yyyy"
            .Range(.Cells(80 + iSoDongCuaTrang * iTrang, "H"), .Cells(136 + iSoDongCuaTrang * iTrang, "I")).NumberFormat = "0.0"
            .Range(.Cells(80 + iSoDongCuaTrang * iTrang, "J"), .Cells(136 + iSoDongCuaTrang * iTrang, "K")).NumberFormat = "0.0"
            .Range(.Cells(111 + iSoDongCuaTrang * iTrang, "D"), .Cells(136 + iSoDongCuaTrang * iTrang, "F")).NumberFormat = "dd-mm-yyyy"
            '==================
            .Range(.Cells(80 + iSoDongCuaTrang * iTrang, "A"), .Cells(106 + iSoDongCuaTrang * iTrang, "Y")).Font.Italic = True
            .Range(.Cells(111 + iSoDongCuaTrang * iTrang, "A"), .Cells(134 + iSoDongCuaTrang * iTrang, "Y")).Font.Italic = True
            .Range(.Cells(75 + iSoDongCuaTrang * iTrang, "A"), .Cells(134 + iSoDongCuaTrang * iTrang, "Y")).HorizontalAlignment = -4108
            .Range(.Cells(75 + iSoDongCuaTrang * iTrang, "A"), .Cells(134 + iSoDongCuaTrang * iTrang, "Y")).VerticalAlignment = -4108
        End With
    End Sub
    Public Sub ExportSoDiaChinh(ByVal app As Object, ByVal maChu As String, ByVal iTrang As Integer, ByVal iSoDongCuaTrang As Integer, ByVal NamIn As Int16, ByVal MaDonViHanhChinh As String)
        'lay thong tin chu su dung
        Dim dt As New DataTable
        Dim parasCSD() As String = {"@Flag", "@MaChuSuDung"}
        Dim ValuesCSD() As String = {1, maChu}

        dt = GetData_SP("spInSoDiaChinhGetCSD", ValuesCSD, parasCSD)
        'Mydata = MyTableSoDiaChinh


        Dim sTen As String = ""
        Dim sCMND As String = ""
        Dim SHoKhau As String = ""
        Dim sDiaChi As String = ""
        Dim sQuocTich As String = ""
        Dim strChuSuDung As String = ""
        For iChu As Integer = 0 To dt.Rows.Count - 1
            'If dr.Item("DoiTuongSDD") IsNot DBNull.Value Then

            '    If dr.Item("DoiTuongSDD").ToString = "GDC" Then
            '        If dr.Item("GioiTinh").ToString <> "" Then
            '            If dr.Item("GioiTinh") = False Then
            '                sTen += "Hộ bà "
            '            Else
            '                sTen += "Hộ ông "
            '            End If
            '        Else
            '            sTen += "Hộ ông "
            '        End If
            '    End If
            'End If

            If dt.Rows(iChu).Item("QuanHe") IsNot DBNull.Value Then
                If dt.Rows(iChu).Item("QuanHe").ToString <> "" Then
                    strChuSuDung += dt.Rows(iChu).Item("QuanHe") & " "
                End If
            End If
            strChuSuDung += dt.Rows(iChu).Item("HoTen")

            If dt.Rows(iChu).Item("NamSinh") IsNot DBNull.Value Then
                If dt.Rows(iChu).Item("NamSinh").ToString <> "" And dt.Rows(iChu).Item("NamSinh").ToString <> 0 Then
                    strChuSuDung += ", sinh năm " & dt.Rows(iChu).Item("NamSinh")
                End If
            End If

            If dt.Rows(iChu).Item("SoDinhDanh") IsNot DBNull.Value Then
                If dt.Rows(iChu).Item("SoDinhDanh").ToString <> "" Then
                    If dt.Rows(iChu).Item("SoDinhDanh").ToString <> "" Then
                        strChuSuDung = strChuSuDung & ", chứng minh thư (hộ chiếu) số " & dt.Rows(iChu).Item("SoDinhDanh")
                    End If
                End If
            End If

            If dt.Rows(iChu).Item("NgayCap") IsNot DBNull.Value Then
                If dt.Rows(iChu).Item("NgayCap").ToString <> "" Then
                    strChuSuDung += " cấp ngày " & dt.Rows(iChu).Item("NgayCap").day() & "/" & dt.Rows(iChu).Item("NgayCap").Month() & "/" & dt.Rows(iChu).Item("NgayCap").Year()
                End If
            End If

            If dt.Rows(iChu).Item("NoiCap") IsNot DBNull.Value Then
                If dt.Rows(iChu).Item("NoiCap").ToString <> "" Then
                    strChuSuDung += " tại " & dt.Rows(iChu).Item("NoiCap").ToString
                End If
            End If
            If dt.Rows(iChu).Item("SoHoKhau") IsNot DBNull.Value Then
                If dt.Rows(iChu).Item("SoHoKhau").ToString <> "" Then
                    strChuSuDung = strChuSuDung & ", sổ hộ khẩu số " & dt.Rows(iChu).Item("SoHoKhau")
                End If
            End If

            If dt.Rows(iChu).Item("NgayCapHoKhau") IsNot DBNull.Value Then
                If dt.Rows(iChu).Item("NgayCapHoKhau").ToString <> "" Then
                    strChuSuDung += " cấp ngày " & dt.Rows(iChu).Item("NgayCapHoKhau").day() & "/" & dt.Rows(iChu).Item("NgayCapHoKhau").Month() & "/" & dt.Rows(iChu).Item("NgayCapHoKhau").Year()
                End If
            End If

            If dt.Rows(iChu).Item("NoiCapHoKhau") IsNot DBNull.Value Then
                If dt.Rows(iChu).Item("NoiCapHoKhau").ToString <> "" Then
                    strChuSuDung += " tại " & dt.Rows(iChu).Item("NoiCapHoKhau")
                End If
            End If

            If dt.Rows(iChu).Item("DiaChi") IsNot DBNull.Value Then
                If dt.Rows(iChu).Item("DiaChi").ToString <> "" Then
                    strChuSuDung = strChuSuDung & ", Địa chỉ: " & dt.Rows(iChu).Item("DiaChi")
                End If
            End If

            If dt.Rows(iChu).Item("QuocTich") IsNot DBNull.Value Then
                If dt.Rows(iChu).Item("QuocTich").ToString <> "" Then
                    strChuSuDung = strChuSuDung & ", Quốc tịch: " & dt.Rows(iChu).Item("QuocTich").ToString
                End If
            End If
            strChuSuDung = strChuSuDung & vbNewLine
        Next
        'Dim dsHoSo As New DataSet
        '@Flag=2
        'cmd.CommandText = " SELECT tblHoSoCapGCN.MaHoSoCapGCN, NgayVaoSo, SoThua, ToBanDo,SoPhatHanhGCN, SoVaoSoCapGCN " & _
        '                  " FROM tblHoSoCapGCN, tblChuSuDung " & _
        '                  " WHERE(tblHoSoCapGCN.MaHoSoCapGCN = tblChuSuDung.MaHoSoCapGCN)" & _
        '                  " AND tblChuSuDung.MaChu = '" & dr.Item("MaChu").ToString & "' " & _
        '                  " ORDER BY NgayVaoSo"
        'da.SelectCommand = cmd
        'da.Fill(dsHoSo)
        Dim dsMucDichSD, dsNguonGocSD As New DataSet
        Dim MyTableHoSo As New DataTable
        Dim parasHoSo() As String = {"@Flag", "@MaChu", "@NamIn", "@MaDVHC"}
        Dim ValuesHoSo() As String = {2, maChu, NamIn, MaDonViHanhChinh}

        MyTableHoSo = GetData_SP("spSoDiaChinh", ValuesHoSo, parasHoSo)


        With app
            .Range(.Cells(69 + iSoDongCuaTrang * iTrang, "A"), .Cells(74 + iSoDongCuaTrang * iTrang, "A")).MergeCells = True
            .Range(.Cells(69 + iSoDongCuaTrang * iTrang, "A"), .Cells(74 + iSoDongCuaTrang * iTrang, "A")).Value = strChuSuDung 'sTen & sCMND & SHoKhau & vbNewLine & sDiaChi & sQuocTich

            Dim Max As Integer = 0
            Dim Max_Tong As Integer = 0

            For j As Integer = 0 To MyTableHoSo.Rows.Count - 1
                'lay ra muc dich va nguon goc cua moi hoso de in trong qua trinh in 1 ho so
                '@flag=3
                'cmd.CommandText = " SELECT DienTichRieng, DienTichChung, tblMucDichSuDungDat.KyHieu , ThoiDiemKetThuc" & _
                '                 " FROM tblHoSoCapGCN, tblMucDichSuDungDat" & _
                '                 " WHERE tblMucDichSuDungDat.MaHoSoCapGCN = tblHoSoCapGCN.MaHoSoCapGCN" & _
                '                 " AND tblHoSoCapGCN.MaHoSoCapGCN ='" & MyTableHoSo.Rows(j).Item("MaHoSoCapGCN").ToString & "'"
                'da.SelectCommand = cmd
                'da.Fill(dsMucDichSD)

                Dim MyTableMucDichSD As New DataTable
                Dim parasMucDichSD() As String = {"@Flag", "@MaHoSoCapGCN", "@NamIn"}
                Dim ValuesMucDichSD() As String = {3, MyTableHoSo.Rows(j).Item("MaHoSoCapGCN").ToString, NamIn}

                MyTableMucDichSD = GetData_SP("spSoDiaChinh", ValuesMucDichSD, parasMucDichSD)

                '@Flag=4
                'cmd.CommandText = " SELECT DISTINCT tblNguonGocSuDungDat.KyHieu" & _
                '                 " FROM tblHoSoCapGCN, tblNguonGocSuDungDat" & _
                '                 " WHERE tblNguonGocSuDungDat.MaHoSoCapGCN = tblHoSoCapGCN.MaHoSoCapGCN" & _
                '                 " AND tblHoSoCapGCN.MaHoSoCapGCN ='" & MyTableHoSo.Rows(j).Item("MaHoSoCapGCN").ToString & "'"
                'da.SelectCommand = cmd
                'da.Fill(dsNguonGocSD)
                Dim MyTableNguonGocSD As New DataTable
                Dim parasNguonGocSD() As String = {"@Flag", "@MaHoSoCapGCN", "@NamIn"}
                Dim ValuesNguonGocSD() As String = {4, MyTableHoSo.Rows(j).Item("MaHoSoCapGCN").ToString, NamIn}
                MyTableNguonGocSD = GetData_SP("spSoDiaChinh", ValuesNguonGocSD, parasNguonGocSD)

                .Range(.Cells(80 + Max_Tong + iSoDongCuaTrang * iTrang, "A"), .Cells(80 + Max_Tong + iSoDongCuaTrang * iTrang, "C")).MergeCells = True
                .Range(.Cells(80 + Max_Tong + iSoDongCuaTrang * iTrang, "A"), .Cells(80 + Max_Tong + iSoDongCuaTrang * iTrang, "C")).Value = String.Format("{0:dd/MM/yyyy}", MyTableHoSo.Rows(j).Item("NgayVaoSoCapGCN"))
                .Range(.Cells(80 + Max_Tong + iSoDongCuaTrang * iTrang, "D"), .Cells(80 + Max_Tong + iSoDongCuaTrang * iTrang, "E")).MergeCells = True
                .Range(.Cells(80 + Max_Tong + iSoDongCuaTrang * iTrang, "D"), .Cells(80 + Max_Tong + iSoDongCuaTrang * iTrang, "E")).Value = MyTableHoSo.Rows(j).Item("SoThua").ToString
                .Range(.Cells(80 + Max_Tong + iSoDongCuaTrang * iTrang, "F"), .Cells(80 + Max_Tong + iSoDongCuaTrang * iTrang, "G")).MergeCells = True
                .Range(.Cells(80 + Max_Tong + iSoDongCuaTrang * iTrang, "F"), .Cells(80 + Max_Tong + iSoDongCuaTrang * iTrang, "G")).Value = MyTableHoSo.Rows(j).Item("ToBanDo").ToString

                .Range(.Cells(80 + Max_Tong + iSoDongCuaTrang * iTrang, "T"), .Cells(80 + Max_Tong + iSoDongCuaTrang * iTrang, "V")).MergeCells = True
                .Range(.Cells(80 + Max_Tong + iSoDongCuaTrang * iTrang, "T"), .Cells(80 + Max_Tong + iSoDongCuaTrang * iTrang, "V")).Value = MyTableHoSo.Rows(j).Item("SoPhatHanhGCN").ToString
                .Range(.Cells(80 + Max_Tong + iSoDongCuaTrang * iTrang, "W"), .Cells(80 + Max_Tong + iSoDongCuaTrang * iTrang, "Y")).MergeCells = True
                .Range(.Cells(80 + Max_Tong + iSoDongCuaTrang * iTrang, "W"), .Cells(80 + Max_Tong + iSoDongCuaTrang * iTrang, "Y")).Value = MyTableHoSo.Rows(j).Item("SoVaoSoCapGCN").ToString

                Dim n As Integer

                For n = 0 To MyTableMucDichSD.Rows.Count - 1
                    .Range(.Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "L"), .Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "M")).MergeCells = True
                    If IsDBNull(MyTableMucDichSD.Rows(n).Item("KyHieu")) Then
                        .Range(.Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "L"), .Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "M")).Value = MyTableMucDichSD.Rows(n).Item("KyHieu").ToString
                    End If
                    ' neu ho so da chu su dung thi chi in dien tich chung (neu co)
                    'Dim dsChuSuChuSuDungHoSo As New DataSet
                    ''@flag=5
                    'cmd.CommandText = "SELECT * FROM tblChuSuDung WHERE MaHoSoCapGCN ='" & MyTableHoSo.Rows(j).Item("MaHoSoCapGCN").ToString & "'"
                    'da.SelectCommand = cmd
                    'da.Fill(dsChuSuChuSuDungHoSo)

                    Dim MyTableChuSuChuSuDungHoSo As New DataTable
                    Dim parasChuSuChuSuDungHoSo() As String = {"@Flag", "@MaHoSoCapGCN", "@NamIn"}
                    Dim ValuesChuSuChuSuDungHoSo() As String = {4, MyTableHoSo.Rows(j).Item("MaHoSoCapGCN").ToString, NamIn}
                    MyTableChuSuChuSuDungHoSo = GetData_SP("spSoDiaChinh", ValuesChuSuChuSuDungHoSo, parasChuSuChuSuDungHoSo)

                    If MyTableChuSuChuSuDungHoSo.Rows.Count = 1 Then
                        .Range(.Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "H"), .Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "I")).MergeCells = True
                        .Range(.Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "H"), .Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "I")).Value = MyTableMucDichSD.Rows(n).Item("DienTichRieng").ToString
                    End If

                    .Range(.Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "J"), .Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "K")).MergeCells = True
                    .Range(.Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "J"), .Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "K")).Value = MyTableMucDichSD.Rows(n).Item("DienTichChung").ToString
                    .Range(.Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "L"), .Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "M")).MergeCells = True
                    .Range(.Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "L"), .Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "M")).Value = MyTableMucDichSD.Rows(n).Item("KyHieu").ToString
                    .Range(.Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "N"), .Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "P")).MergeCells = True
                    .Range(.Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "N"), .Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "P")).Value = MyTableMucDichSD.Rows(n).Item("ThoiHan").ToString
                Next
                For n = 0 To MyTableNguonGocSD.Rows.Count - 1
                    .Range(.Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "Q"), .Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "S")).MergeCells = True
                    .Range(.Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "Q"), .Cells(80 + n + Max_Tong + iSoDongCuaTrang * iTrang, "S")).Value = MyTableNguonGocSD.Rows(n).Item("KyHieu").ToString
                Next

                Max = MyTableMucDichSD.Rows.Count

                If Max < MyTableNguonGocSD.Rows.Count Then Max = MyTableNguonGocSD.Rows.Count

                Max_Tong += Max
                If j <> MyTableHoSo.Rows.Count - 1 Then
                    .Range(.Cells(80 + iSoDongCuaTrang * iTrang + Max_Tong - 1, "A"), .Cells(80 + iSoDongCuaTrang * iTrang + Max_Tong - 1, "Y")).Borders(&H9).Weight = &H1
                End If


                Dim MyTableDangKyBienDong As New DataTable
                Dim parasDangKyBienDong() As String = {"@Flag", "@MaHoSoCapGCN", "@NamIn"}
                Dim ValuesDangKyBienDong() As String = {6, MyTableHoSo.Rows(j).Item("MaHoSoCapGCN").ToString, NamIn}
                MyTableDangKyBienDong = GetData_SP("spSoDiaChinh", ValuesDangKyBienDong, parasDangKyBienDong)
                Dim k As Integer
                For k = 0 To MyTableDangKyBienDong.Rows.Count - 1
                    If MyTableDangKyBienDong.Rows.Count = 1 Then
                        .Range(.Cells(111 + k + Max_Tong + iSoDongCuaTrang * iTrang, "A"), .Cells(111 + k + Max_Tong + iSoDongCuaTrang * iTrang, "C")).MergeCells = True
                        .Range(.Cells(111 + k + Max_Tong + iSoDongCuaTrang * iTrang, "A"), .Cells(111 + k + Max_Tong + iSoDongCuaTrang * iTrang, "C")).Value = MyTableDangKyBienDong.Rows(k).Item("SoThua").ToString
                    End If
                    .Range(.Cells(111 + k + Max_Tong + iSoDongCuaTrang * iTrang, "A"), .Cells(111 + k + Max_Tong + iSoDongCuaTrang * iTrang, "C")).MergeCells = True
                    .Range(.Cells(111 + k + Max_Tong + iSoDongCuaTrang * iTrang, "A"), .Cells(111 + k + Max_Tong + iSoDongCuaTrang * iTrang, "C")).Value = MyTableDangKyBienDong.Rows(k).Item("SoThua").ToString
                    .Range(.Cells(111 + k + Max_Tong + iSoDongCuaTrang * iTrang, "D"), .Cells(111 + k + Max_Tong + iSoDongCuaTrang * iTrang, "F")).MergeCells = True
                    .Range(.Cells(111 + k + Max_Tong + iSoDongCuaTrang * iTrang, "D"), .Cells(111 + k + Max_Tong + iSoDongCuaTrang * iTrang, "F")).Value = String.Format("{0:dd/MM/yyyy}", MyTableDangKyBienDong.Rows(k).Item("ThoiDiemDangKy").ToString)
                    .Range(.Cells(111 + k + Max_Tong + iSoDongCuaTrang * iTrang, "G"), .Cells(111 + k + Max_Tong + iSoDongCuaTrang * iTrang, "Y")).MergeCells = True
                    .Range(.Cells(111 + k + Max_Tong + iSoDongCuaTrang * iTrang, "G"), .Cells(111 + k + Max_Tong + iSoDongCuaTrang * iTrang, "Y")).Value = MyTableDangKyBienDong.Rows(k).Item("NoidungSoBienDong").ToString

                Next
            Next
            '            .Range(.Cells(80 + iSoDongCuaTrang * iTrang + k, "A"), .Cells(80 + iSoDongCuaTrang * iTrang + k, "Y")).Borders(&H9).Weight = &H1
            '.Range(.Cells(80 + iSoDongCuaTrang * iTrang + Max, "A"), .Cells(80 + iSoDongCuaTrang * iTrang + Max, "Y")).Borders(&H9).Weight = &H1
        End With
    End Sub
    Public Sub ExportMucLucSoDiaChinh(ByVal app As Object, ByVal iTrang As Integer, ByVal iSoDongCuaTrang As Integer)
        With app
            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "A"), .Cells(68 + 3 + iSoDongCuaTrang * iTrang, "Y")).BorderAround(, 2, -4105)
            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "A"), .Cells(68 + 67 + iSoDongCuaTrang * iTrang, "Y")).BorderAround(, 2, -4105)
            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "A"), .Cells(68 + 67 + iSoDongCuaTrang * iTrang, "B")).BorderAround(, 2, -4105)
            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "J"), .Cells(68 + 67 + iSoDongCuaTrang * iTrang, "M")).BorderAround(, 2, -4105)
            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "P"), .Cells(68 + 67 + iSoDongCuaTrang * iTrang, "U")).BorderAround(, 2, -4105)
            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "V"), .Cells(68 + 67 + iSoDongCuaTrang * iTrang, "Y")).BorderAround(, 2, -4105)
            .Range(.Cells(68 + 2 + iSoDongCuaTrang * iTrang, "J"), .Cells(68 + 67 + iSoDongCuaTrang * iTrang, "K")).BorderAround(, 2, -4105)
            .Range(.Cells(68 + 2 + iSoDongCuaTrang * iTrang, "L"), .Cells(68 + 67 + iSoDongCuaTrang * iTrang, "M")).BorderAround(, 2, -4105)
            .Range(.Cells(68 + 2 + iSoDongCuaTrang * iTrang, "V"), .Cells(68 + 67 + iSoDongCuaTrang * iTrang, "W")).BorderAround(, 2, -4105)
            .Range(.Cells(68 + 2 + iSoDongCuaTrang * iTrang, "X"), .Cells(68 + 67 + iSoDongCuaTrang * iTrang, "Y")).BorderAround(, 2, -4105)

            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "A"), .Cells(68 + 3 + iSoDongCuaTrang * iTrang, "B")).MergeCells = True
            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "C"), .Cells(68 + 3 + iSoDongCuaTrang * iTrang, "I")).MergeCells = True

            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "J"), .Cells(68 + 1 + iSoDongCuaTrang * iTrang, "M")).MergeCells = True
            .Range(.Cells(68 + 2 + iSoDongCuaTrang * iTrang, "J"), .Cells(68 + 3 + iSoDongCuaTrang * iTrang, "K")).MergeCells = True
            .Range(.Cells(68 + 2 + iSoDongCuaTrang * iTrang, "L"), .Cells(68 + 3 + iSoDongCuaTrang * iTrang, "M")).MergeCells = True

            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "N"), .Cells(68 + 3 + iSoDongCuaTrang * iTrang, "O")).MergeCells = True
            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "P"), .Cells(68 + 3 + iSoDongCuaTrang * iTrang, "U")).MergeCells = True

            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "V"), .Cells(68 + 1 + iSoDongCuaTrang * iTrang, "Y")).MergeCells = True
            .Range(.Cells(68 + 2 + iSoDongCuaTrang * iTrang, "V"), .Cells(68 + 3 + iSoDongCuaTrang * iTrang, "W")).MergeCells = True
            .Range(.Cells(68 + 2 + iSoDongCuaTrang * iTrang, "X"), .Cells(68 + 3 + iSoDongCuaTrang * iTrang, "Y")).MergeCells = True

            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "A"), .Cells(68 + 3 + iSoDongCuaTrang * iTrang, "Y")).Font.Bold = True
            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "A"), .Cells(68 + 3 + iSoDongCuaTrang * iTrang, "Y")).HorizontalAlignment = -4108 'Constants.xlCenter
            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "A"), .Cells(68 + 3 + iSoDongCuaTrang * iTrang, "Y")).VerticalAlignment = -4108 ' Constants.xlCenter

            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "A"), .Cells(68 + 3 + iSoDongCuaTrang * iTrang, "B")).Value = "Số thứ tự"
            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "C"), .Cells(68 + 3 + iSoDongCuaTrang * iTrang, "I")).Value = "Tên người sử dụng đất"

            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "J"), .Cells(68 + 1 + iSoDongCuaTrang * iTrang, "M")).WrapText = True
            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "J"), .Cells(68 + 1 + iSoDongCuaTrang * iTrang, "M")).Value = "Đăng ký tại            sổ địa chính"
            .Range(.Cells(68 + 2 + iSoDongCuaTrang * iTrang, "J"), .Cells(68 + 3 + iSoDongCuaTrang * iTrang, "K")).Value = "Quyển số"
            .Range(.Cells(68 + 2 + iSoDongCuaTrang * iTrang, "L"), .Cells(68 + 3 + iSoDongCuaTrang * iTrang, "M")).Value = "Trang số"

            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "N"), .Cells(68 + 3 + iSoDongCuaTrang * iTrang, "O")).Value = "Số thứ tự"
            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "P"), .Cells(68 + 3 + iSoDongCuaTrang * iTrang, "U")).Value = "Tên người sử dụng đất"

            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "V"), .Cells(68 + 1 + iSoDongCuaTrang * iTrang, "Y")).WrapText = True
            .Range(.Cells(68 + iSoDongCuaTrang * iTrang, "V"), .Cells(68 + 1 + iSoDongCuaTrang * iTrang, "Y")).Value = "Đăng ký tại            sổ địa chính"
            .Range(.Cells(68 + 2 + iSoDongCuaTrang * iTrang, "V"), .Cells(68 + 3 + iSoDongCuaTrang * iTrang, "W")).Value = "Quyển số"
            .Range(.Cells(68 + 2 + iSoDongCuaTrang * iTrang, "X"), .Cells(68 + 3 + iSoDongCuaTrang * iTrang, "Y")).Value = "Trang số"
        End With
    End Sub
#End Region

    Public Sub Config(ByVal app As Object, ByVal KhoGiay As String, ByVal KieuIn As String)

        If KhoGiay = "a3" Then
            KhoGiay = 8
        ElseIf KhoGiay = "a4" Then
            KhoGiay = 9
        End If

        If KieuIn = "ngang" Then
            KieuIn = &H2 ' Land
        ElseIf KieuIn = "dung" Then
            KieuIn = &H1 ' Port
        End If


        With app
            Try
                .Workbooks.Add()
                .Sheets.Add()
                .ActiveSheet.Name = "BaoCao"
                .Cells.Font.Name = "Times New Roman"
                Dim s As Double = KhoGiay.TrimEnd.TrimStart
                .Cells.Worksheet.PageSetup.PaperSize = KhoGiay.TrimEnd.TrimStart
                .Cells.Worksheet.PageSetup.Orientation = KieuIn.TrimEnd.TrimStart 'XlPageOrientation.xlLandscape
                '.Cells.Worksheet.PageSetup.DifferentFirstPageHeaderFooter = True //cai nay ko co trong excel
                '.Cells.Worksheet.PageSetup.FitToPagesTall = 1
                '.Cells.Worksheet.PageSetup.FitToPagesWide = 1

                .Cells.Worksheet.PageSetup.FooterMargin = 25.08
                .Cells.Worksheet.PageSetup.TopMargin = 70.56 '0.98"
                .Cells.Worksheet.PageSetup.LeftMargin = 84.96 '1.18"
                .Cells.Worksheet.PageSetup.BottomMargin = 70.56
                .Cells.Worksheet.PageSetup.RightMargin = 56.88 '0.79"
                .Cells.Worksheet.PageSetup.FirstPageNumber = 0
                If KieuIn = &H2 Then ' ngang
                    .Cells.Worksheet.PageSetup.HeaderMargin = 70 '25.08 '0.39"
                Else
                    .Cells.Worksheet.PageSetup.HeaderMargin = 50
                End If

            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try

            .Cells.Worksheet.PageSetup.RightHeader = "Trang số: &P&         " ' của  &N"  
            ' .Cells.Font.Size = 12
        End With
    End Sub
    Public Function DuongDanFile() As String
        Dim sFileName As String = ""

        If Now.Day() < 10 Then
            sFileName += "0" & CStr(Now.Day())
        Else
            sFileName += CStr(Now.Day())
        End If


        If Now.Month() < 10 Then
            sFileName += "0" & CStr(Now.Month())
        Else
            sFileName += CStr(Now.Month())
        End If

        sFileName += CStr(Now.Year())

        If Now.Hour() < 10 Then
            sFileName += "0" & CStr(Now.Hour())
        Else
            sFileName += CStr(Now.Hour())
        End If

        If Now.Minute() < 10 Then
            sFileName += "0" & CStr(Now.Minute())
        Else
            sFileName += CStr(Now.Minute())
        End If

        If Now.Millisecond() < 10 Then
            sFileName += "0" & CStr(Now.Second())

        Else
            sFileName += CStr(Now.Second())
        End If

        Return sFileName
    End Function
    Public Sub LuuFile(ByVal app As Object, ByVal fileName As String)



        'frmInSoDiaChinh.SfDialog.Filter = "Excel 2007 Workbook (*.xlsx)|*.xlsx|Excel 97-2003 Workbook (*.xls)|*.xls"
        'frmInSoDiaChinh.SfDialog.FileName = sName & sFileName
        'frmInSoDiaChinh.SfDialog.ShowDialog()
        With app
            Dim frm As New ctrInSo
            If fileName <> "" Then
                .ActiveCell.Worksheet.SaveAs(fileName)
                ' .Workbooks.Open(fileName)
                .Visible = True

                '.ActiveCell.Worksheet.ClearArrows()
                '.ActiveCell.Worksheet.ClearCircles()
                '.ActiveCell.Worksheet.Delete()
            Else
                .ActiveCell.Worksheet.ClearArrows()
                .ActiveCell.Worksheet.ClearCircles()
                .ActiveCell.Worksheet.Delete()
            End If
        End With

        app = Nothing

    End Sub

    Public Function GetData_SP(ByVal sp As String, ByVal Values() As String, ByVal paras() As String) As DataTable
        Dim strError As String = ""
        Dim MyTable As New DataTable
        Try
            'Khoi tao doi tuong DMC.clsDatabase
            Dim Database As New clsDatabase
            Database.Connection = strConnection
            If (Database.OpenConnection) Then
                'Chắc chắn rằng mảng giá trị truyền vào tương đương với mảng tham biến
                If (paras.Length <> Values.Length) Then
                    Return Nothing
                End If
                'Gọi phương thức GetValue của đối tượng clsDatabase
                Database.getValue(MyTable, sp, paras, Values)
                Database.CloseConnection()
            End If
        Catch ex As Exception
            strError = ex.Message
        End Try
        Return MyTable
    End Function
End Class
